Create a dynamic list of INSERT statement values

Question

I am writing a stored procedure to create a set of instructions DELETEfor the administrator who should work with the database

As part of the rollback solution, I would like each line that I am going to delete to also create a separate statement separately INSERT, so that the user running the script wants to cancel, they can just run insert statements on the database

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

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]'

SELECT . INSERT, :

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, '') + ')'

, INSERT, .

Answer 2

, , , . : -

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)

: -

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

: -

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)

( ), insert. - - - ( ).

factory (.. SP, ), ).

, - , .

Answer 3

temprory, , . , ( ) .

, .

Answer 4

, SSMS Tools Pack

This is a Microsoft SQL Server Management Studio (SSMS) add-in for versions 2005, 2008, 2008 R2, and 2012.

It can do many things, including: Create an insert statement for a table, view, or the entire database (see the function page: Insert operator generator ).

I myself used it to accomplish the same thing you ask: create a backup for a large number of data records so that we can easily undo our changes.