sp_generate_inserts for SQL Server 2008

Question

I've been using Narayana Vyas Kondreddi's excellent stored procedure sp_generate_inserts http://vyaskn.tripod.com/code/generate_inserts.txt in a SQL Server 2005 database.

But after moving to SQL Server 2008 I get weird results where a long whitespace is inserted after UNIQUEIDENTIFIER values:

INSERT INTO [BannerGroups]([Id], [DescriptionText], [Width], [Height]) 
VALUES('BFCD0173-9432-47D1-84DF-8AB3FB40BF76                                                                                                                                                                                                                           ', 'Example', 145, NULL)

Anyone know how to fix this?

Answer 1

Appears to be this section, just over half way down:

WHEN @Data_Type IN ('uniqueidentifier') 
                THEN  
                    'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'

See it's converting to a CHAR(255) which means the value is being padded out to 255 characters. Change that to VARCHAR instead and it should be fine as that will not pad the values out with spaces.

Answer 2

Since SQL Server 2008 we can generate the INSERT scripts via Generate Script utility itself.

For more detailed answer check out - What is the best way to auto-generate INSERT statements for a SQL Server table?