May 18, 2011 at 12:48 pm
vivdix (11/10/2009)
Hi Florian,Still waiting for your reply, may be you bit busy but please take some time to make it run on MSSQL 2K.
Thanks
Vivek
Here's a reworked script versioned for SQL 2000 server using a table named 'LogTable'. I didn't test it out extensively, but it did at least produce the desired scripted data.
Regards,
bitBIG
/******************************************************************
Author
======
Florian Reischl
Summary
=======
Script to create a SELECT statement to script all data of a specified table
Parameters
==========
@table_name
The name of the table to be scripted
@handle_big_binary
If set to 1 the user defined function udf_varbintohexstr_big will be used
to convert BINARY, VARBINARY and IMAGE data. For futher information see remarks.
@column_names
If set to 0 only the values to be inserted will be scripted; the column names wont.
This saves memory but the destination tables needs exactly the same columns in
same order.
If set to 1 also the names of the columns to insert the values into will be scripted.
Remarks
=======
Attention:
In case of colums of type BINARY, VARBINARY or IMAGE
you either need the user defined function udf_varbintohexstr_big
and option @handle_big_binary set to 1 or you risk a loss of data
if the data of a cell are larger than 3998 bytes
Data type sql_variant is not supported.
History
=======
V01.00.00.00 (2009-01-15)
* Initial release
V01.01.00.00 (2009-01-25)
* Added support for IMAGE columns with user defined function udf_varbintohexstr_big
V01.01.01.00 (2009-02-04)
* Fixed bug for NTEXT and XML
V01.02.00.00 (2009-02-21)
* Added possibility to script column names
******************************************************************/
SET NOCOUNT ON
DECLARE @table_name SYSNAME
DECLARE @handle_big_binary BIT
DECLARE @column_names BIT
-- ////////////////////
-- -> Configuration
SET @table_name = 'dbo.LogTable'
SET @handle_big_binary = 1
SET @column_names = 1
-- <- Configuration
-- ////////////////////
DECLARE @object_id INT
DECLARE @schema_id INT
--SELECT * FROM sys.all_objects
--SELECT @object_id = object_id, @schema_id = schema_id
-- FROM sys.tables
-- WHERE object_id = OBJECT_ID(@table_name)
--SELECT @object_id = id, @schema_id = id
-- FROM sysobjects
-- WHERE id = OBJECT_ID(@table_name)
SELECT @object_id = id, @schema_id = id
FROM sysobjects
WHERE id = OBJECT_ID(@table_name) and type = 'U'
DECLARE @columns TABLE (column_name SYSNAME, ordinal_position INT, data_type SYSNAME, data_length INT, is_nullable BIT)
-- Get all column information
INSERT INTO @columns
SELECT column_name, ordinal_position, data_type, character_maximum_length, CASE WHEN is_nullable = 'YES' THEN 1 ELSE 0 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE --TABLE_SCHEMA = OBJECT_NAME(@schema_id)
-- AND
TABLE_NAME = OBJECT_NAME(@object_id)
DECLARE @select VARCHAR(8000)
DECLARE @insert VARCHAR(8000)
DECLARE @crlf CHAR(2)
DECLARE @sql VARCHAR(8000)
DECLARE @first BIT
DECLARE @pos INT
SET @pos = 1
SET @crlf = CHAR(13) + CHAR(10)
WHILE EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position >= @pos)
BEGIN
DECLARE @column_name SYSNAME
DECLARE @data_type SYSNAME
DECLARE @data_length INT
DECLARE @is_nullable BIT
-- Get information for the current column
SELECT @column_name = column_name, @data_type = data_type, @data_length = data_length, @is_nullable = is_nullable
FROM @columns
WHERE ordinal_position = @pos
-- Create column select information to script the name of the source/destination column if configured
IF (@select IS NULL)
SET @select = ' ''' + QUOTENAME(@column_name)
ELSE
SET @select = @select + ','' + ' + @crlf + ' ''' + QUOTENAME(@column_name)
-- Handle NULL values
SET @sql = ' '
SET @sql = @sql + 'CASE WHEN ' + QUOTENAME(@column_name) + ' IS NULL THEN ''NULL'' ELSE '
-- Handle the different data types
IF (@data_type IN ('bigint', 'bit', 'decimal', 'float', 'int', 'money', 'numeric',
'real', 'smallint', 'smallmoney', 'tinyint'))
BEGIN
SET @sql = @sql + 'CONVERT(VARCHAR(40), ' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type IN ('char', 'nchar', 'nvarchar', 'varchar'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(' + QUOTENAME(@column_name) + ', '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type = 'date')
BEGIN
SET @sql = @sql + '''CONVERT(DATE, '' + master.dbo.fn_varbintohexstr (CONVERT(BINARY(3), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'time')
BEGIN
SET @sql = @sql + '''CONVERT(TIME, '' + master.dbo.fn_varbintohexstr (CONVERT(BINARY(5), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME, '' + master.dbo.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime2')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME2, '' + master.dbo.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'smalldatetime')
BEGIN
SET @sql = @sql + '''CONVERT(SMALLDATETIME, '' + master.dbo.fn_varbintohexstr (CONVERT(BINARY(4), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'text')
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(VARCHAR(8000), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('ntext', 'xml'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(NVARCHAR(8000), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('binary', 'varbinary'))
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (' + QUOTENAME(@column_name) + ')'
ELSE
SET @sql = @sql + ' master.dbo.fn_varbintohexstr (' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type = 'timestamp')
BEGIN
SET @sql = @sql + '''CONVERT(TIMESTAMP, '' + master.dbo.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'uniqueidentifier')
BEGIN
SET @sql = @sql + '''CONVERT(UNIQUEIDENTIFIER, '' + master.dbo.fn_varbintohexstr (CONVERT(BINARY(16), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'image')
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
ELSE
SET @sql = @sql + ' master.dbo.fn_varbintohexstr (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
END
ELSE
BEGIN
PRINT 'ERROR: Not supported data type: ' + @data_type
RETURN
END
SET @sql = @sql + ' END'
-- Script line end for finish or next column
IF EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position > @pos)
SET @sql = @sql + ' + '', '' +'
ELSE
SET @sql = @sql + ' + '
-- Remember the data script
IF (@insert IS NULL)
SET @insert = @sql
ELSE
SET @insert = @insert + @crlf + @sql
SET @pos = @pos + 1
END
-- Close the column names select
SET @select = @select + ''' +'
-- Print the INSERT INTO part
PRINT 'SELECT ''INSERT INTO ' + @table_name + ''' + '
-- Print the column names if configured
IF (@column_names = 1)
BEGIN
PRINT ' ''('' + '
PRINT @select
PRINT ' '')'' + '
END
PRINT ' '' VALUES ('' +'
-- Print the data scripting
PRINT @insert
-- Script the end of the statement
PRINT ' '')'''
PRINT ' FROM ' + @table_name
July 16, 2012 at 1:25 pm
I've had an issue with this script duplicating columns when there is a gap in the colid of the syscolumns table in SQL Server 2000.
Is there an easy fix for this?
Thanks,
-Robert
September 26, 2012 at 10:32 pm
where is the code of udf_varbintohexstr_big?
September 27, 2012 at 11:34 am
dave.villanueva25 (9/26/2012)
where is the code of udf_varbintohexstr_big?
My test of the script on SQL 2000 didn't involve huge varbinary or image, hence I didn't look into this further. Regardless, this perhaps may be what you're referring to:
http://www.sqlservercentral.com/scripts/Hexadecimal/65997/[/url]
Be sure to look into the discussion/comments therein as well.
/bitBIG
December 18, 2013 at 6:03 am
Very nice. Thanks for sharing.
December 11, 2014 at 9:52 am
Florian, thank you very much for this script! I was preparing to post a question on SSC and needed to include some table data to demonstrate my query, and this worked a treat! I could have sworn that I had a script with similar functionality in my toolkit, but it was nowhere to be found. Until now.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
May 4, 2015 at 1:35 pm
I'm struggling with the datetime2 format.
I get this message:
Msg 8152, Level 16, State 17, Line 1
String or binary data would be truncated.
from this line:
CASE WHEN [Entered_Dttm] IS NULL THEN 'NULL' ELSE 'CONVERT(DATETIME2, ' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), [Entered_Dttm])) + ')' END + ', ' +
of this select statement:
SELECT 'INSERT INTO D_FORMATTED_ITEM_CODE' +
'(' +
'[Formatted_Item_Code_Id],' +
'[Item_Code_GUID],' +
'[Formatted_Item_Code],' +
'[Format_Type_Id],' +
'[Entered_Dttm],' +
'[Entered_By]' +
')' +
' VALUES (' +
CASE WHEN [Formatted_Item_Code_Id] IS NULL THEN 'NULL' ELSE 'CONVERT(UNIQUEIDENTIFIER, ' + master.sys.fn_varbintohexstr (CONVERT(BINARY(16), [Formatted_Item_Code_Id])) + ')' END + ', ' +
CASE WHEN [Item_Code_GUID] IS NULL THEN 'NULL' ELSE 'CONVERT(UNIQUEIDENTIFIER, ' + master.sys.fn_varbintohexstr (CONVERT(BINARY(16), [Item_Code_GUID])) + ')' END + ', ' +
CASE WHEN [Formatted_Item_Code] IS NULL THEN 'NULL' ELSE '''' + REPLACE([Formatted_Item_Code], '''', '''''') + '''' END + ', ' +
CASE WHEN [Format_Type_Id] IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR(40), [Format_Type_Id]) END + ', ' +
CASE WHEN [Entered_Dttm] IS NULL THEN 'NULL' ELSE 'CONVERT(DATETIME2, ' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), [Entered_Dttm])) + ')' END + ', ' +
CASE WHEN [Entered_By] IS NULL THEN 'NULL' ELSE '''' + REPLACE([Entered_By], '''', '''''') + '''' END +
')'
FROM D_FORMATTED_ITEM_CODE
is there any easy type conversion I can use?
412-977-3526 call/text
May 4, 2015 at 2:46 pm
It looks like a BINARY(9) works
this doesn't work
CASE WHEN [Entered_Dttm] IS NULL THEN 'NULL' ELSE 'CONVERT(DATETIME2, ' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), [Entered_Dttm])) + ')' END + ', ' +
this does
CASE WHEN [Entered_Dttm] IS NULL THEN 'NULL' ELSE 'CONVERT(DATETIME2, ' + master.sys.fn_varbintohexstr (CONVERT(BINARY(9), [Entered_Dttm])) + ')' END + ', ' +
412-977-3526 call/text
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply