Generate several sql insert from an sql select sentence

Question

How can I generate some insert sql sentences using a specific select query. Are there any tool from Ms Sql Server 2008? or else?

Answer 1

Something like this will generate a list of INSERT statements. You then copy and paste from the output and run the INSERTs. It's far from ideal, but it works.

SELECT  'INSERT table (col1, col2)
        VALUES (''' + stringcolumn + ''', ' + CAST(intcolumn AS varchar) + ')'
FROM    sourcetable

Answer 2

Do you mean something like this?

SELECT
    'INSERT INTO MyTable (Col1, Col2, Col3) VALUES (' + Col1 + ', ' + Col2 + ', ' + Col3 + ')'
FROM
    MyTable

Of course you will need to encapsulate some fields in quotes, perform some char escaping and convert numbers to varchars, etc.

Answer 3

watch out for nulls and data type conversions (SQL Server syntax):

SELECT
    'INSERT INTO YourTable (Col_int, Col_datetime, Col_varchar) VALUES ('
        +COALESCE(CONVERT(varchar(30),Col_int),'null')
        +','+COALESCE(''''+CONVERT(char(23),Col_datetime,121)+'''','null')
        +','+COALESCE(''''+Col_varchar+'''','null')
        +')'
    FROM YourTable
    WHERE ...

Answer 4

Why do you want to do it row by row? That is a very poor technique when you can insert them all in one query. Databases are designed to perform set operations much faster than row-by-row operations.

insert table1 (field1, field2)
Select Field1, Field2 from Table2 where field3 >10