6

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Niels Bosma
  • 11,758
  • 29
  • 89
  • 148

2 Answers2

9

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.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200
  • Does anyone know why would you convert to a 255 char/varchar since the GUIDs are just 32? I've changed it to that and it seems to do the job well. https://gist.github.com/aterreno/33e4a7ff95b3337d96ff469d1ed76e9c – Antonio Terreno May 12 '21 at 10:05
1

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?

Community
  • 1
  • 1
vmvadivel
  • 1,041
  • 5
  • 7