Create SQL INSERT Script with values gathered from table

Question

I need to create a INSERT script in order to insert in another database the same data.
If in SQL Server I select "Script table as > INSERT To" I can easily recreate the skeleton for the INSERT statement. However since I have several records to migrate, I would prefer to avoid having to insert the values manually.

Therefore is there any way to "automatically" get the INSERT script with also the values (coming from the target table) filled in?

I know this could be done with SSIS, but I am wondering whether it would be possible as well with a quicker solution.

D'Arcy Rittich · Accepted Answer · 2011-09-22 13:08:33Z

113

You can do this with SSMS.

1 - Right-click your database in Object Explorer.
2 - Select Tasks/Generate Scripts...
3 - On the Set Scripting Options page, click the Advanced button and make sure Types of data to script is set to Data only.

The resulting script will have a USE DATABASE statement at the top. Change this to the database you would like to insert the data into.

answered Sep 22, 2011 at 13:08

D'Arcy Rittich

172k41 gold badges298 silver badges287 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Aaron Bertrand Over a year ago

FYI you can turn the USE Database command off under Tools / Options / SQL Server Object Explorer / Scripting / General scripting options / Script USE <database>

Alan B Over a year ago

You'll also need to remove any identity columns manually from the generated T-SQL if you're using the output as a template to insert new records, or set IDENTITY_INSERT on.

Robin Over a year ago

I realize you say explicitly this, but you have to right click on the DATABASE, not the connection or the table.

gbn · Accepted Answer · 2011-09-22 13:04:34Z

6

Use the free SSMS tools pack to "generate insert statements"?

Or in SSMS (don't have it on this PC to confirm) the export wizards allows you to "script data" too

answered Sep 22, 2011 at 13:04

gbn

434k84 gold badges602 silver badges690 bronze badges

3 Comments

Francesco Over a year ago

The export wizard allows me to export the data, but not the statement with the data. But +1 for the Tool, looks very good!

gbn Over a year ago

@Luca: the tool is essential! Check RedFilter's answer about the SSMS wizard.

Seb Over a year ago

SSMS is no longer free for 2012+

Aaron Bertrand · Accepted Answer · 2011-09-22 13:06:37Z

2

It will depend on the data types, because you need to conditionally enclose string values in quotes or cast numeric values as strings. You also need to deal with problem characters:

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

Vyas has a pretty complex stored procedure for this purpose.

Of course you can do this much easier by just 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" an insert, you can just run it...

answered Sep 22, 2011 at 13:06

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

3 Comments

Francesco Over a year ago

The second approach would be very easy, if only would not be on a completely different intranet/server without a remote access :-(

HLGEM Over a year ago

@Luca, in that case export a file and bring it in with SSIS or the import Export wizard.

Francesco Over a year ago

@HLGEM: the solution provided by RedFilter is the closes to what I was looking for, since it is the most direct. But thanks for the hint.

user610217 · Accepted Answer · 2011-09-22 13:05:12Z

0

use the INSERT INTO ... SELECT format:

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

answered Sep 22, 2011 at 13:05

user610217

Comments

Vishal · Accepted Answer · 2019-07-16 18:10:51Z

0

@RedFilter, your solution is working like a charm for less data SIZE.

In my case, When I tried to open the Exported sql file, I faced the OutOfMemoryException.

The file size is around 4GB.

In order to get the data from that file, I tried BCP(Bulk Copy Program) approach.

Hope that would be help full for someone.

answered Jul 16, 2019 at 18:10

Vishal

1,4083 gold badges14 silver badges32 bronze badges

Collectives™ on Stack Overflow

Create SQL INSERT Script with values gathered from table

5 Answers 5

3 Comments

3 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related