58

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.

Francesco
  • 9,947
  • 7
  • 67
  • 110

5 Answers5

107

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.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • 4
    FYI you can turn the `USE Database` command off under Tools / Options / SQL Server Object Explorer / Scripting / General scripting options / Script USE – Aaron Bertrand Sep 22 '11 at 13:34
  • 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. – Alan B Sep 18 '15 at 12:04
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

gbn
  • 422,506
  • 82
  • 585
  • 676
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...

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • The second approach would be very easy, if only would not be on a completely different intranet/server without a remote access :-( – Francesco Sep 22 '11 at 13:29
  • @Luca, in that case export a file and bring it in with SSIS or the import Export wizard. – HLGEM Sep 22 '11 at 14:00
  • @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. – Francesco Sep 22 '11 at 14:05
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.

enter image description here

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.

Kms
  • 1,082
  • 2
  • 11
  • 27
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/

Jeremy Holovacs
  • 22,480
  • 33
  • 117
  • 254