SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Script all data of a table


Script all data of a table

Author
Message
bitBIG
bitBIG
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 189
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


Robert.Sterbal
Robert.Sterbal
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 2000
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
dave.villanueva25
dave.villanueva25
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
where is the code of udf_varbintohexstr_big?
bitBIG
bitBIG
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 189
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/

Be sure to look into the discussion/comments therein as well.

/bitBIG
Sean Smith (SSC)
Sean Smith (SSC)
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1452 Visits: 1012
Very nice. Thanks for sharing.
Wayne West
Wayne West
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5730 Visits: 3702
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.

-----
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson
robert.sterbal 56890
robert.sterbal 56890
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2388 Visits: 1236
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?
robert.sterbal 56890
robert.sterbal 56890
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2388 Visits: 1236
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 + ', ' +
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search