November 7, 2009 at 6:10 am
Hi Vivek
Unfortunately I don't have a SS2k test environment at the moment. I'll try it on 2000 on Monday, when I'm back at work.
Greets
Flo
November 10, 2009 at 2:08 am
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
November 12, 2009 at 9:08 pm
vivek-459197 (11/7/2009)
Hi Florian,Resolve the issue replacing -
DECLARE @select VARCHAR(MAX)
With
DECLARE @select VARCHAR(8000)
&
changing this query
SELECT @object_id = object_id, @schema_id = schema_id
FROM sys.tables
WHERE object_id = OBJECT_ID(@table_name)
to
SELECT @object_id = id, @schema_id = schema_id
FROM sysobjects
WHERE id = OBJECT_ID(@table_name) and type = 'U'
But still getting error at schema_id
Please help on this.
Thanks
Vivek
It would be real handy if you'd post the actual error you're getting...
What do you get when you execute the following?
SELECT * FROM dbo.SysObjects
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2009 at 12:24 am
Hi Florian,
First of all congratulation, it's a nice work/script, end very useful for many of uss!
One thing that's missing, and I hope you will add to the script is the case of IDETITY column.
Yes I know what to do or, what to change in the destination table to make the script working,
but for many others it will be a huge help to resolve that issue in the script.
bye
January 5, 2010 at 1:52 pm
Very nice job! I modified the script a bit to make it a stored proc that spits out the insert statements automatically. I'm always writing scripts such as this or looking for them on the internet to make my job easier - you did a nice job with this one.
Here is the modified script:
CREATE PROC dbo.pScriptTableData
@TableName SYSNAME,
@handle_big_binary BIT = 1,
@column_names BIT = 1
AS
BEGIN
--Converted to Stored Procedure by RHanson 1/5/2010
--EXEC pScriptTableData 'dbo.DimReseller'
/******************************************************************
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 --Removed by RHanson 1/5/2010 - Variable moved to SP Param
--DECLARE @column_names BIT --Removed by RHanson 1/5/2010 - Variable moved to SP Param
DECLARE @SQLStmt VARCHAR(MAX) --Added by RHanson 1/5/2010
-- ////////////////////
-- -> Configuration
SET @table_name = @TableName
-- <- 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 @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 ' + 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.sys.fn_varbintohexstr (CONVERT(BINARY(3), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'time')
BEGIN
SET @sql = @sql + '''CONVERT(TIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(5), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime2')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME2, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'smalldatetime')
BEGIN
SET @sql = @sql + '''CONVERT(SMALLDATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(4), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'text')
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(VARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('ntext', 'xml'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(NVARCHAR(MAX), ' + 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.sys.fn_varbintohexstr (' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type = 'timestamp')
BEGIN
SET @sql = @sql + '''CONVERT(TIMESTAMP, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'uniqueidentifier')
BEGIN
SET @sql = @sql + '''CONVERT(UNIQUEIDENTIFIER, '' + master.sys.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.sys.fn_varbintohexstr (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
END
ELSE
BEGIN
PRINT 'ERROR: Not supported data type: ' + @data_type
RETURN
END
-- Script line end for finish or next column
IF EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position > @pos)
SET @sql = @sql + ' + '', '' +'
ELSE
-- 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 + ''' +'
/* - SECTION REMOVED BY RHanson 1/5/2010 and replaced by automated execution script below
-- 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
*/
--SECTION ADDED BY RHanson 1/5/2010 to support automated execution of script
-- Print the INSERT INTO part
SET @SQLStmt = 'SELECT ''INSERT INTO ' + @table_name + ''' + '
-- Print the column names if configured
IF (@column_names = 1)
BEGIN
SET @SQLStmt = @SQLStmt + ' ''('' + '
SET @SQLStmt = @SQLStmt + @select
SET @SQLStmt = @SQLStmt + ' '')'' + '
END
SET @SQLStmt = @SQLStmt + ' ''VALUES ('' +'
-- Print the data scripting
SET @SQLStmt = @SQLStmt + @insert
-- Script the end of the statement
SET @SQLStmt = @SQLStmt + ' '')'''
SET @SQLStmt = @SQLStmt + ' FROM ' + @table_name
PRINT @SQLStmt
EXEC(@SQLStmt)
END
January 5, 2010 at 5:41 pm
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[/url]
January 5, 2010 at 6:29 pm
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.
February 8, 2010 at 5:38 pm
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<
February 10, 2010 at 3:46 am
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
February 10, 2010 at 10:00 am
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<
October 19, 2010 at 7:04 pm
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
-- Script line end for finish or next column
IF EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position > @pos)
SET @sql = @sql + ' + '', '' +'
ELSE
-- 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
February 22, 2011 at 8:44 am
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!
February 23, 2011 at 10:31 am
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.
May 17, 2011 at 8:15 am
Thanks for sharing this script. It works great and is going to be a big help in the future.
May 17, 2011 at 4:36 pm
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)
SELECTColumn1, 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.
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply