Sp_generate_inserts for SQL Server 2008

Question

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

But after switching to SQL Server 2008, I get strange results when long spaces are inserted after the UNIQUEIDENTIFIER values:

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

Does anyone know how to fix this?

Answer 1

This section appears, just over half way down:

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

Look at the conversion to CHAR (255), which means the value is populated with up to 255 characters. Change it to VARCHAR and it should be good, as it will not expose values ​​with spaces.

Answer 2

Starting with SQL Server 2008, we can generate INSERT scripts using the Generate Script utility itself.

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