I updated spu_generateinsert again today when one of my colleagues reminded me that I needed to prefix my strings with a capital N to ensure that any unicode values go into the database correctly.

So now instead of the call spu_generateinsert @table = 'LanguageData' producing
INSERT INTO [LanguageData] ([liID], [Value])VALUES (7,'D''Artagnan raconte qu''à sa première visite à')
it produces
INSERT INTO [LanguageData] ([liID], [Value])VALUES (7,N'D''Artagnan raconte qu''à sa première visite à')

As usual, the script is available here
And again as usual, leave me a comment if you think of some new functionality I should include, or any issues you come across.

Further to my earlier update today I've reworked the printing out of the insert statements - mainly because I need it for the work I'm doing today...

It now produces a single statement per row of data, and then checks the DATALENGTH of that statement. If it exceeds the 8000 byte limit, then it looks for CHAR(10)/CHAR(13) characters and breaks the row into chunks based on these CHAR(10)/CHAR(13) limits.

This relies on the fact that
PRINT 'Hello ' + CHAR(13) + 'World'
and
PRINT 'Hello'
PRINT 'World'
both produce the same output of
Hello
World

So, instead of PRINTing CHAR(13) we're PRINTing multiple lines.

All of which seems to work and I can finally get the

As per usual, download the spu_generateInsert script here.

As mentioned in my update yesterday about spu_generateInsert there is a limitation on the TSQL PRINT function which limits the output to 8000 bytes.

I'm about to try and make the procedure work within this limitation (a table I'm trying to script exceeds this), but in the meantime I've added an additional bit of error reporting which results in the final line in the messages window being displayed as:
-- ** WARNING: The data length for at least one row exceeds 8000 bytes. The PRINT command is limited to 8000 bytes (for more information see http://msdn.microsoft.com/en-us/library/ms176047.aspx). Do not trust this data. ** --
so at least the user is informed of the issue.

Download the updated script here.

As always, comments and suggestions are welcomed.

Earlier today I had a user error using spu_generateinsert and couldn't work out what was wrong. It turns out I had specified the table name wrongly. So, I've updated the procedure to check for the existence of the table and to report if there isn't one. Hopefully, this will stop me making the same mistake again :-)

Download the updated script here.

I also noticed today that the PRINT command only outputs 8000 bytes worth of data - so if the contents of a VARCHAR(MAX) is larger than this the output will be truncated. I'm mulling over various fixes for this but in the mean time have noted it as a known issue in the SQL.

One of my colleagues suggested an update to spu_generateinsert before Christmas, and today was the first time I got a chance to script it. The suggestion was to prevent IDENTITY columns from being scripted.

I've added a new parameter to the procedure, named @GenerateIdentityColumn, which I have set to default to 1. This means it maintains the current behaviour. If you set it to 0 though, you will no longer get the
SET IDENTITY_INSERT [tablename] ON
and
SET IDENTITY_INSERT [tablename] OFF
statements, and the identity column itself will not be included in the script, which means that the database will assign the relevant value.

Example usage is
exec spu_GenerateInsert @table ='users', @generateGo=0, @restriction='columna = x', @producesingleinsert=0, @debug=0, @GenerateOneLinePerColumn=0, @GenerateIdentityColumn=0

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.

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 2005 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.

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.

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.

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.

I had a bit of spare time today, so I've updated spu_generateInsert.sql to fix a couple of annoyances:

• 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 put comments into the stored procedure to indicate the changes.

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