Build a dynamic list of INSERT statement values

sql, sql-server-2008

Solution

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.

Problem

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) ```

Original source