Build a dynamic list of INSERT statement values

Question

I am writing a stored procedure to create a set of DELETE statements for an administrator to run against a database

As part of the "rollback" solution, I would like, for every row I am going to delete, to also create, separately, a corresponding INSERT statement so that should the person running the script wish to undo, they can simply run the insert statements against the database

My question is, if I have a table with say 8 columns (Col1..Col8), how can I extract the values in a comma separated list for all columns so that I end up with

INSERT INTO Table VALUES (Col1value, Col2value, ...Col8value)

Answer 1

Consider the following command:

SELECT 'SELECT ' +
    STUFF ((
        SELECT ', [' + name + ']'
        FROM syscolumns
        WHERE id = OBJECT_ID('Table') AND
            name <> 'me'
        FOR XML PATH('')), 1, 1, '') +
    ' FROM [Table]'

That will build a SELECT statement for a specific table. To build an INSERT statement it might look like this:

SELECT @sql = 'INSERT INTO [Table] (' +
    STUFF ((
        SELECT ', [' + name + ']'
        FROM syscolumns
        WHERE id = OBJECT_ID('Table') AND
            name <> 'me'
        FOR XML PATH('')), 1, 1, '') +
    ') VALUES (' +
    STUFF ((
        SELECT ', @' + name
        FROM syscolumns
        WHERE id = OBJECT_ID('Table') AND
            name <> 'me'
        FOR XML PATH('')), 1, 1, '') + ')'

There are of course many ways to get to the INSERT statement, mold it for your liking.

Answer 2

While waiting for a generic solution from other experts, here's an expedient version that is appropriate if the table structure is known and stable. This test data:-

create table #mytable (
    col1value varchar (50),
    col2value int,
    col3value decimal(10, 5),
    col4value date,
    col5value datetime
);

insert into #mytable values 
    (null,1,234.567,'2013-12-20','2013-01-07T17:30:24.567'),
    ('def',null,987.645,'2013-12-21','2013-01-01'),
    ('abc',17,null,'2013-12-20','2013-10-07T11:31:21.517'),
    ('jkl',2,3232.32,null,'2013-01-01 01:02:03.456'),
    ('mno',2,7.4006,'2012-07-04',null)

Allows this code:-

select 'insert into #myTable values (' +
    isnull('''' + col1value + '''','null') + ',' +
    isnull(convert(varchar(50),col2value),'null') + ',' + 
    isnull(convert(varchar(50),col3value),'null') + ',' +
    isnull('''' + convert(varchar(50),col4value) + '''','null') + ',' +
    isnull('''' + convert(varchar(50),col5value,121) + '''','null') + 
    ')'
from #mytable

To produce:-

insert into #myTable values (null,1,234.56700,'2013-12-20','2013-01-07 17:30:24.567')
insert into #myTable values ('def',null,987.64500,'2013-12-21','2013-01-01 00:00:00.000')
insert into #myTable values ('abc',17,null,'2013-12-20','2013-10-07 11:31:21.517')
insert into #myTable values ('jkl',2,3232.32000,null,'2013-01-01 01:02:03.457')
insert into #myTable values ('mno',2,7.40060,'2012-07-04',null)

You can protect against structure changes (to some degree) by including a column list in the insert statements. Even with a column list - if someone adds a non nullable column to the table without a default value - your inserts are going to fail (unless you massage them as the schema is changed).

I would ALWAYS use a code factory to produce this sort of code (i.e. an SP that runs over the information schema - that uses the column types to build the code to serialise each type of value).

The example above only handles a few of the types that you may need to handle - but you should be able to work out how to approach all of the other types.