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
Giammarco Schisani
Giammarco Schisani
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 12
Nice script, thanks for sharing. For what concerns SQL Server Management Studio data limitations, can you not change the settings in "Options -> Query Results -> Results to Grid and Results to Text ??

_______________________
Giammarco Schisani
Volpet Software - Table Diff
hanson.richard
hanson.richard
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 6
You could change that option, but text will limit the amount of data returned per row/column to 8192. However, in SQL Server Management Studio the default length that text returns per column is 256, so you will likely truncate your data pretty quickly when building the complete insert statement for each row. Even at 8192 a row of data can be 8192 characters on its own, let alone the additional data that is being applied to each returned column (INSERT INTO .... VALUES....).

If you use the grid, a column can return 65,635 characters by default which should handle ALL instances of the INSERT statement, the columns and the data associated with each column.
Lisa Slater Nicholls
Lisa Slater Nicholls
SSC-Addicted
SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)

Group: General Forum Members
Points: 445 Visits: 617
RH says:

[65,635 chars] which should handle ALL


Hmm, no, I wish. Not for columns of XML type holding XML documents.

IAC I'm not able to figure out a way to write substring logic something like udf_varbintohexstr_big to chunk up large XML documents properly, or anything similar. Sending results to a file doesn't work, or maybe I'm doing something wrong.

Anybody have a clue? I agree this is a great script in general, and Florian has obviously figured out a way to handle large blobs for varbinaries. Casting or converting XML to a MAX size variable should be fine, also, so I think it's just a question of figuring out the right way to receive the results.

Anybody ?

Thanks,

>L<
Florian Reischl
Florian Reischl
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3521 Visits: 3934
Hi MSzI

I'm currently working on an updated version which handles IDENTITY-Inserts as configurable




Hi Lisa

I don't think you can use SSMS to handle BLOBs like XML files. I'd suggest using SQLCMD instead which should have no restrictions. If even SQLCMD has restrictions for result row lengths, you can use a Powershell script or any simple .NET application.

Greets
Flo


The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Lisa Slater Nicholls
Lisa Slater Nicholls
SSC-Addicted
SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)SSC-Addicted (445 reputation)

Group: General Forum Members
Points: 445 Visits: 617
Thanks Flo. That's what I thought. It is not a serious restriction, because your method is sound. I'm just pointing out that the any length limitation -- whether 64k or 255 characters -- is going to remain a problem. >L<
Ross Crawford
Ross Crawford
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 98
Nice script, thanks! It fails when the table has a lot of columns though, as the @insert variable overflows the limit of NVARCHAR(MAX). I have amended it to use a table var to get around this, probably should also do this for the @select var, although it's less likely that will reach the limit.


/******************************************************************

Author
======
Florian Reischl

Summary
=======
Script to create a SELECT statement to script all data of a specified table

Usage
=====
Open the script on the database to script data from.

* Configure the name of the table to be scripted (@table_name)
* Configure if the udf_varbintohexstr_big is available (only needed for IMAGE and VARBINARY data with more than 3998 bytes)
* Configure if the column names shall be scripted for destination database. This brings more flexibility because the destination table has more columns than the source table or the column order is different. Do not script the column names to save space.
* Execute the script once.
* Take the execution result as statement to script your data (maybe change something withi)
* Execute the result from first execution again
* Take the scripted data to insert them on another database/server.

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
V01.02.01.00 (2010-10-20) R Crawford
* Fix bug when too many columns overflows NVARCHAR(MAX)

******************************************************************/

SET NOCOUNT ON

DECLARE @table_name SYSNAME
DECLARE @handle_big_binary BIT
DECLARE @column_names BIT

-- ////////////////////
-- -> Configuration
SET @table_name = 'dbo.tblUserProfile'
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)


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 = SCHEMA_NAME(@schema_id)
AND TABLE_NAME = OBJECT_NAME(@object_id)

DECLARE @select VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @insert_tbl TABLE ([insert] VARCHAR(MAX))
DECLARE @crlf CHAR(2)
DECLARE @sql VARCHAR(MAX)
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 ' + @table_name + '.' + 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), ' + @table_name + '.' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type IN ('char', 'nchar', 'nvarchar', 'varchar'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(' + @table_name + '.' + QUOTENAME(@column_name) + ', '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type = 'date')
BEGIN
SET @sql = @sql + '''CONVERT(DATE, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(3), ' + @table_name + '.' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'time')
BEGIN
SET @sql = @sql + '''CONVERT(TIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(5), ' + @table_name + '.' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + @table_name + '.' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime2')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME2, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + @table_name + '.' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'smalldatetime')
BEGIN
SET @sql = @sql + '''CONVERT(SMALLDATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(4), ' + @table_name + '.' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'text')
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(VARCHAR(MAX), ' + @table_name + '.' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('ntext', 'xml'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(NVARCHAR(MAX), ' + @table_name + '.' + 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 (' + @table_name + '.' + QUOTENAME(@column_name) + ')'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (' + @table_name + '.' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type = 'timestamp')
BEGIN
SET @sql = @sql + '''CONVERT(TIMESTAMP, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + @table_name + '.' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'uniqueidentifier')
BEGIN
SET @sql = @sql + '''CONVERT(UNIQUEIDENTIFIER, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(16), ' + @table_name + '.' + 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), ' + @table_name + '.' + QUOTENAME(@column_name) + '))'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (CONVERT(VARBINARY(MAX), ' + @table_name + '.' + 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
INSERT @insert_tbl ([insert]) VALUES (@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
DECLARE Insert_Cursor CURSOR FOR
SELECT [insert] FROM @insert_tbl
OPEN Insert_Cursor
FETCH NEXT FROM Insert_Cursor INTO @insert;
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT @insert
FETCH NEXT FROM Insert_Cursor INTO @insert;
END

CLOSE Insert_Cursor
DEALLOCATE Insert_Cursor

-- Script the end of the statement
PRINT ' '')'''
PRINT ' FROM ' + @table_name




Regards

ROSCO
Patrick2525
Patrick2525
SSC-Enthusiastic
SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)SSC-Enthusiastic (143 reputation)

Group: General Forum Members
Points: 143 Visits: 180
I think I'm the only rider on the small schoolbus for this one... but I'm missing something. Can someone give some examples of when you would need to use this script?

Thanks!
ssaari
ssaari
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 61
This is good, except I needed to change the data type names to lower case in the declaration statements. I suspect this may be because my collation is CS.
upstart
upstart
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 327
Thanks for sharing this script. It works great and is going to be a big help in the future.
fahey.jonathan
fahey.jonathan
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 400
Thanks for the script. It seems to cover most cases easily. I do have a suggestion. I recently worked on importing data from a similar script and found that SSMS could not open the file file because it was too large. I had to open the file in Notepad and copy and paste large blocks of insert statements into SSMS. Most of the data in the file was the column list, not the values list.

Your script adds the complete column list for every line, which will produce the same problem:
INSERT INTO TableName (Column1, Column2) VALUES (...)



It would reduce the size of the output file to eliminate the redundant column list by using the multiple VALUES clause:
INSERT INTO TableName (Column1, Column2)
SELECT Column1, Column2
FROM (VALUES
(1, 'Apples'),
(2, 'Bananas'),
(3, 'Oranges')
) AS x (Column1, Column2)



I tried your script on one of my narrower tables, and the column list was 334 characters while the VALUES list was 176 characters. The DML was twice as long as the values being manipulated. For my table of 1000 rows, the original script would generate an output file of 510,000 characters (510 characters per statement, 1000 statements). Eliminating the repeated column list would produce an output file of 176,668 characters (176 characters per line for 1000 statements, plus the 334 column list twice). That is about 65% size reduction, meaning that we could output three times as much data before SSMS ran into a size constraint on the script file. Of course, column name length to datalength will change for each table, but it is something to consider.
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