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/%5B/url%5D
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 38 (of 38 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy