Create SQL INSERT Script with values ​​collected from a table

Question

I need to create an INSERT script to paste the same data into another database.
If in SQL Server I select "Script table as> INSERT To", I can easily recreate the skeleton for the INSERT expression. However, since I have several records for the transfer, I would prefer not to enter the values ​​manually.

Therefore, is there a way to “automatically” get an INSERT script with filled values ​​(coming from the target table)?

I know that this can be done using SSIS, but I am wondering if this is possible with a faster solution.

Answer 1

You can do this using SSMS.

1 - Right-click your database in Object Explorer.
2 - Task Selection / Scripting ...
3 - On the "Script Set Parameters" page, click the "Advanced" button and make sure that the script data types are set to "Data Only".

As a result, the script will have a USE DATABASE statement at the top. Change this to the database into which you would like to insert the data.

Answer 2

Use the free SSMS toolkit to "create insert statements"?

Or in SSMS (you do not need to be confirmed on this PC), export wizards can also "script data"

Answer 3

This will depend on the data types, because you need to conditionally enclose string values ​​in quotation marks or enter numeric values ​​as strings. You also need to deal with characteristic characters:

 SELECT 'INSERT INTO dbo.DestinationTable(col1, col2, col3) SELECT ' + CONVERT(VARCHAR(12), col1) + ',' + '''' + REPLACE(col2, '''', '''''') + ''',' + '''' + REPLACE(col3, '''', '''''') + ''';' FROM dbo.SourceTable; 

Vyas has a rather complicated stored procedure for this purpose.

Of course, you can make it a lot easier by simply saying:

 INSERT INTO OtherDatabase.dbo.DestinationTable(col1, col2, col3) SELECT col1, col2, col3 FROM dbo.SourceTable; 

In other words, you don’t need to "script" paste, you can just run it ...

Answer 4

use the format INSERT INTO ... SELECT:

http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

Answer 5

@RedFilter, your solution works like a charm for smaller data.

In my case, when I tried to open the exported sql file, I encountered an OutOfMemoryException exception.

The file size is about 4 GB.

To get data from this file, I tried the BCP (Bulk Copy Program) approach.

Hope this will be a complete help for someone.