In the past I usually hand generated a quick bit of SQL to generate my INSERT statements from existing table data like so:
select
‘insert into dbo.Links values (‘ +
cast(exceedence_set_id as varchar(100)) + ‘, ‘ +
cast(seq as varchar(100)) + ‘, ‘ +
cast(link_id as varchar(100)) + ‘, ‘ +
cast(link_offset as varchar(100)) + ‘, ‘ +
‘false,’ +
cast(band_id as varchar(100)) +
‘)’
from dbo.Links
The only problem with this was that I had to hand add in the column name, handle null data etc… So, I thought I’d have a look to see if someone with loads more SQL experience than me had had a go at handling this all automatically. After a bit of searching on Google I found a handy procedure from Narayana Vyas Kondreddi. Put simply it can automatically generate your Insert statements for any table and also comes with a host of configurable parameters to tailor the output to your own needs. For example to generate the statements for table Links but ommit the column names because the data will be inserted into an identical table on another SQL Server instance, and to also ommit the identity column, call the procedure like so:
EXEC sp_generate_inserts ‘Links’, @include_column_list = 0, @ommit_identity = 1
It’s that simple….great piece of work by Vyas. Also check out the rest of his site as there’s plenty more SQL stuff available…