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])
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.