Use a SELECT to Print a Bunch of INSERT INTOs

Question

I have a bunch of records I want to move to another database and I just want to create a bunch of inserts that I can copy and paste. I've seen someone do this before but I can't figure it out. I'm not getting the escapes right.

It's something like this where 'Code', 'Description' and 'Absent' are the columns I want from the table.

 SELECT 'INSERT INTO AttendanceCodes
          (Code, Description, Absent) 
         VALUES 
          (' + Code + ',' + Description  + ',' + Absent')'
  FROM AttendanceCodes 

The end result should be a slew of INSERTS with the correct values like this:

 INSERT INTO AttendanceCodes 
   (Code, Description, Absent) 
 VALUES 
   ('A','Unverified Absence','UA')

Answer 1

Check this

SELECT 'INSERT INTO AttendanceCodes (Code, Description, Absent) VALUES (''' + Code + ''',''' + Description  + ''',''' + Absent''')' 
  FROM AttendanceCodes  

Answer 2

try something like this:

 SELECT 
    'INSERT INTO AttendanceCodes (Code, Description, Absent) VALUES (' 
        + ISNULL(''''+CONVERT(varchar(8000),Code)+'''','null')
        + ',' 
        + ISNULL(''''+CONVERT(varchar(8000),Description)+'''','null')
        + ',' 
        + ISNULL(''''+CONVERT(varchar(8000),Absent)+'''','null')
        +')'
    FROM AttendanceCodes 

This will handle NULLs. I'm not sure of your columns data types, but you can only concatenate strings, so I forced everything to varchar(8000), you can modify this as necessary.

Answer 3

A useful SSMS addin to generate this sort of script is SSMS Tool Pack http://www.ssmstoolspack.com/

Answer 4

For a generic stored procedure that takes a table name (and some other options) and generates insert statements for current data in that table, check out this handy link.

It creates a procedure called sp_generate_inserts. You pass this procedure the table name, and it will generate all the insert statements to recreate the data.