Jane's Technical Stuff
Archives
May 2002March 2003
May 2003
July 2003
August 2003
June 2005
July 2005
August 2005
December 2005
January 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
May 2008
June 2008
July 2008
August 2008
September 2008
October 2008
November 2008
Wednesday, August 20, 2008
Generating insert statements from table data - Updated
Yesterday Christian left me a comment containing an enhancement for the spu_generateinsert SQL. His suggestion was adding a new parameter @GenerateOneLinePerColumn to allow more "pretty" SQL to be produced - producing all the columns on separate lines which makes it easier for file comparisons to spot differences when comparing data values. I thought it made sense, so this morning I used an hour of my Madgex ILP time to make this change.
By calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 0 it produces
-- ** Start of Inserts
INSERT INTO [Detail] ([ID], [Age]) VALUES (1,36)
INSERT INTO [Detail] ([ID], [Age]) VALUES (2,40)
-- ** End of Inserts
but by calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 1 it produces
-- ** Start of Inserts
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
1,
36
)
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
2,
40
)
-- ** End of Inserts
Thanks to Christian for this suggestion. The updated script can be found here and related blog posts can be found here.
Note: this is now only working on SQL 2000 as during my testing I found that VARCHAR(8000) just wasn't long enough so I've made it VARCHAR(max). This is the only SQL 2005 specific piece of SQL in this procedure and I've put comments in the code to indicate -- change this to be (8000) for SQL Server 2000.
By calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 0 it produces
-- ** Start of Inserts
INSERT INTO [Detail] ([ID], [Age]) VALUES (1,36)
INSERT INTO [Detail] ([ID], [Age]) VALUES (2,40)
-- ** End of Inserts
but by calling EXEC spu_GenerateInsert @table = 'Detail',@GenerateOneLinePerColumn = 1 it produces
-- ** Start of Inserts
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
1,
36
)
INSERT INTO [Detail]
(
[ID],
[Age]
)
VALUES
(
2,
40
)
-- ** End of Inserts
Thanks to Christian for this suggestion. The updated script can be found here and related blog posts can be found here.
Note: this is now only working on SQL 2000 as during my testing I found that VARCHAR(8000) just wasn't long enough so I've made it VARCHAR(max). This is the only SQL 2005 specific piece of SQL in this procedure and I've put comments in the code to indicate -- change this to be (8000) for SQL Server 2000.
Labels: spu_generateinsert, SQLServer, SQLServer2005
// posted by Jane @ 6:36 PM 
Comments:
Wednesday, January 23, 2008
Update to spu_generateinsert - use of UNION SELECT
Jon Green from 4R Systems Inc left a comment earlier proposing a suggested improvement to spu_generateinsert.
He suggested that it would be easier to scan the produced SQL if it made use of SELECT and UNION SELECT rather than producing a whole host of INSERT statements. This changes the output from:
INSERT INTO [TableName] ([ColumnA], [ColumnB]) VALUES ('A','B')
INSERT INTO [TableName] ([ColumnA], [ColumnB]) VALUES ('C','D')
and replaces it with
INSERT INTO [TableName] ([ColumnA], [ColumnB])
SELECT 'A','B'
UNION SELECT 'C','D'
This is a great suggestion and I have implemented it by the use of an additional parameter @producesingleinsert. By default this is set to 0 which produces the separate INSERT statements as it has done all along. If it is specified as 1, however, it now produces a single statement making use of SELECT and UNION SELECT. I have implemented this as an optional parameter so as not to break any reliance on this functionality.
I also made another change today which is to exclude calculated columns. This has involved joining INFORMATION_SCHEMA.COLUMNS to syscolumns via sysobjects to make use of syscolumns.IsComputed.
The final change was the addition of another optional parameter @ debug. Again this defaults to 0, but if set to 1 prints out the SELECT clause which will be used to obtain the data. This can help to debug problems with the procedure, both in terms of the internal logic, but also with regards to checking that the criteria specified is being handled correctly.
As ever this is work in progress, so please continue sending in your suggestions and I'll attempt to implement them.
Download the script from here.
He suggested that it would be easier to scan the produced SQL if it made use of SELECT and UNION SELECT rather than producing a whole host of INSERT statements. This changes the output from:
INSERT INTO [TableName] ([ColumnA], [ColumnB]) VALUES ('A','B')
INSERT INTO [TableName] ([ColumnA], [ColumnB]) VALUES ('C','D')
and replaces it with
INSERT INTO [TableName] ([ColumnA], [ColumnB])
SELECT 'A','B'
UNION SELECT 'C','D'
This is a great suggestion and I have implemented it by the use of an additional parameter @producesingleinsert. By default this is set to 0 which produces the separate INSERT statements as it has done all along. If it is specified as 1, however, it now produces a single statement making use of SELECT and UNION SELECT. I have implemented this as an optional parameter so as not to break any reliance on this functionality.
I also made another change today which is to exclude calculated columns. This has involved joining INFORMATION_SCHEMA.COLUMNS to syscolumns via sysobjects to make use of syscolumns.IsComputed.
The final change was the addition of another optional parameter @ debug. Again this defaults to 0, but if set to 1 prints out the SELECT clause which will be used to obtain the data. This can help to debug problems with the procedure, both in terms of the internal logic, but also with regards to checking that the criteria specified is being handled correctly.
As ever this is work in progress, so please continue sending in your suggestions and I'll attempt to implement them.
Download the script from here.
Labels: spu_generateinsert, SQL, SQLServer2005
// posted by Jane @ 6:52 PM
Comments:
Tuesday, January 15, 2008
More on spu_generateinsert
Dave posted a comment on the original post the other day seeking for help with resolving an error he was having running the script. The problem transpired to be with dealing with columns of data type image. On investigation, I discovered that there were quite a few column types which wouldn't work as they hadn't been catered for.
I've updated the script to work with all the data types I can get it to work with. For images, binary and varbinary columns, it attempts to insert NULL and puts a warning at the bottom of the generated script saying:
-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Images are not supported by this script at this time. or
-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Binary and VarBinary are not supported by this script at this time. as appropriate
This isn't great, but is at least informative and explains what has happened.
I will try and think about a way of scripting out data from columns of type image, binary and varbinary, but at least, in the meantime the script no longer errors (at least to my knowledge - if you know otherwise, then please let me know and I'll make more fixes).
Download updated spu_generateinsert.
I've updated the script to work with all the data types I can get it to work with. For images, binary and varbinary columns, it attempts to insert NULL and puts a warning at the bottom of the generated script saying:
-- ** WARNING: There is an image column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Images are not supported by this script at this time. or
-- ** WARNING: There is a binary or varbinary column in your table which has not been migrated - this has been replaced with NULL. You will need to do this by hand. Binary and VarBinary are not supported by this script at this time. as appropriate
This isn't great, but is at least informative and explains what has happened.
I will try and think about a way of scripting out data from columns of type image, binary and varbinary, but at least, in the meantime the script no longer errors (at least to my knowledge - if you know otherwise, then please let me know and I'll make more fixes).
Download updated spu_generateinsert.
Labels: spu_generateinsert, SQL, SQLServer, SQLServer2005
// posted by Jane @ 6:32 PM
Comments:
Monday, January 07, 2008
Another update - Generate Insert statement from table for SQL Server
Bruce used my spu_generateinsert script today and identified a further issue, it didn't handle GUIDs properly. I fixed that and did a spot of further testing on one of my test databases and discovered a collation issue, so I fixed that as well whilst I was at it. The result is that it runs slower now, but specifies COLLATE database_default on each column to ensure that there are no "Cannot resolve collation conflict for column 1 in SELECT statement" errors raised.
As before, download it here and feel free to feed back any issues/improvements etc.
As before, download it here and feel free to feed back any issues/improvements etc.
Labels: spu_generateinsert, SQLServer, SQLServer2005
// posted by Jane @ 6:35 PM
Comments:
Thursday, January 03, 2008
Update - Generate Insert statement from table for SQL Server
I had a bit of spare time today, so I've updated spu_generateInsert.sql to fix a couple of annoyances:
I've tested this on SQL Server 2005 but I'd recommend testing it and checking it yourself before relying on it.
Download it here
- it now handles text and ntext appropriately - wrapping the data in single quotes, and escaping any single quotes as appropriate - it also displays a warning indicating that text data will be converted into varchar(8000)
- it now handles NULL values appropriately, and so the step in my previous post on this procedure about "replace of 'NULL' with NULL" can now be safely ignored.
I've tested this on SQL Server 2005 but I'd recommend testing it and checking it yourself before relying on it.
Download it here
Labels: spu_generateinsert, SQL, SQLServer, SQLServer2005
// posted by Jane @ 6:32 PM
Comments:
Tuesday, November 13, 2007
Generate SQL Insert statement from table data
Years ago I stumbled upon a helpful SQL script (SQL Server 2000 and 2005) written by a chap called Keith E Kratochvil which you could point at a table in your database and which would then generate a series of Insert scripts.
I made some amendments at the time and over the next few years to make a bit more useful to me for some specific requirements, including the ability to take bits of a where clause to restrict the data coming back, allowing me to script subsets of data. I've carried this script with me from company to company, and decided it was time to share the updated script.
One word of advice, before running the generated inserts, do a replace of 'NULL' with NULL as I failed to get NULLs appended to a string without causing the whole string to become NULL.
The procedure is available here so feel free to take a look and use it if you find it helpful.
I made some amendments at the time and over the next few years to make a bit more useful to me for some specific requirements, including the ability to take bits of a where clause to restrict the data coming back, allowing me to script subsets of data. I've carried this script with me from company to company, and decided it was time to share the updated script.
One word of advice, before running the generated inserts, do a replace of 'NULL' with NULL as I failed to get NULLs appended to a string without causing the whole string to become NULL.
The procedure is available here so feel free to take a look and use it if you find it helpful.
Labels: spu_generateinsert, SQL, SQLServer, SQLServer2005
// posted by Jane @ 5:39 PM
Comments:
Hi Jane.
Ran the sp and got the following errors:
-- ** Start of Inserts
Msg 536, Level 16, State 5, Procedure sup_GenerateInsert, Line 151
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.
-- ** End of Inserts
Probably "user error" but I would appreciate any insights you might be able to offer.
Thanks.
Ran the sp and got the following errors:
-- ** Start of Inserts
Msg 536, Level 16, State 5, Procedure sup_GenerateInsert, Line 151
Invalid length parameter passed to the SUBSTRING function.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'FROM'.
-- ** End of Inserts
Probably "user error" but I would appreciate any insights you might be able to offer.
Thanks.
Hi Dave
You haven't left me an email address or any other way to contact you, and your blogger profile is switched off or similar, so I'm hoping you're going to head back here and take a look.
There is a section in the script which looks like the following:
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @table
--SELECT @SelList AS TheSelList
I recommend uncommenting the "SELECT @SelList AS TheSelList" line to help enable debugging.
I'd be interested in seeing what is being generated, and the structure of the table you're running it against if you have the time. I'll then try and sort out the problem and update the stored procedure and hopefully fix the problem. Drop me a line at jane(@)dallaway.com - without the ()s of course :-) with any information you have to help.
Thanks
Jane
You haven't left me an email address or any other way to contact you, and your blogger profile is switched off or similar, so I'm hoping you're going to head back here and take a look.
There is a section in the script which looks like the following:
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @table
--SELECT @SelList AS TheSelList
I recommend uncommenting the "SELECT @SelList AS TheSelList" line to help enable debugging.
I'd be interested in seeing what is being generated, and the structure of the table you're running it against if you have the time. I'll then try and sort out the problem and update the stored procedure and hopefully fix the problem. Drop me a line at jane(@)dallaway.com - without the ()s of course :-) with any information you have to help.
Thanks
Jane
Hi Jane,
A possible enhancement or improvement would be to use the UNION operator instead of Values. You would then have one SQL statement to execute instead of multiple. In addition, the script would generate much less text (only one INSERT INTO line) and make it easier to scan the data.
I modified the print out section of script to do this. I haven't tested it thoroughly, but it seems like a pretty straightforward modification. I removed one of the carriage returns so there's no space between UNION lines. Thanks for the script, it was helpful.
Jon Green - 4R Systems Inc.
Here's the code I modified.
--Cursor through the data to generate the INSERT statement / VALUES clause
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @TableData
if (@@fetch_status <> -1)
Print @InsertStmt + char(13) +
'SELECT ' + @TableData
FETCH NEXT FROM CR_Data INTO @TableData
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'UNION SELECT ' + @TableData
IF @generateGo = 1
BEGIN
PRINT 'GO'
END
END
FETCH NEXT FROM CR_Data INTO @TableData
END
CLOSE CR_Data
DEALLOCATE CR_Data
A possible enhancement or improvement would be to use the UNION operator instead of Values. You would then have one SQL statement to execute instead of multiple. In addition, the script would generate much less text (only one INSERT INTO line) and make it easier to scan the data.
I modified the print out section of script to do this. I haven't tested it thoroughly, but it seems like a pretty straightforward modification. I removed one of the carriage returns so there's no space between UNION lines. Thanks for the script, it was helpful.
Jon Green - 4R Systems Inc.
Here's the code I modified.
--Cursor through the data to generate the INSERT statement / VALUES clause
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @TableData
if (@@fetch_status <> -1)
Print @InsertStmt + char(13) +
'SELECT ' + @TableData
FETCH NEXT FROM CR_Data INTO @TableData
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'UNION SELECT ' + @TableData
IF @generateGo = 1
BEGIN
PRINT 'GO'
END
END
FETCH NEXT FROM CR_Data INTO @TableData
END
CLOSE CR_Data
DEALLOCATE CR_Data
# posted by 
: 5:38 PM
: 5:38 PM
Thanks for your suggestion Jon. I've implemented a change based on what you've suggested. I've blogged a reasonable description of what I've changed, and why - but basically I've added a new optional parameter @producesingleinsert to control whether multiple INSERT statements are generated, or a single statement using INSERT, SELECT and UNION SELECT.
New script available here
New script available here
Hi, very usefull script, I added one more parameter to it:
@GenerateOneLinePerColumn BIT = 0
If you want each columns being generated in a separate line, this is usefull to easily compare the generated file with a source control system.
I have all the code available if somebody wants it, most of it consist of statement like :
+ CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13)+' ELSE '' END +
@GenerateOneLinePerColumn BIT = 0
If you want each columns being generated in a separate line, this is usefull to easily compare the generated file with a source control system.
I have all the code available if somebody wants it, most of it consist of statement like :
+ CASE WHEN @GenerateOneLinePerColumn = 1 THEN ' + CHAR(13)+' ELSE '' END +
