Exporting data In SQL Server as INSERT INTO

sql sql-server insert data-migration
Jack Kada picture Jack Kada · Oct 4, 2009 · Viewed 422.5k times · Source

I am using SQL Server 2008 Management Studio and have a table I want to migrate to a different db server.

Is there any option to export the data as an insert into SQL script??

Answer

marc_s picture marc_s · Oct 4, 2009

In SSMS in the Object Explorer, right click on the database, right-click and pick "Tasks" and then "Generate Scripts".

This will allow you to generate scripts for a single or all tables, and one of the options is "Script Data". If you set that to TRUE, the wizard will generate a script with INSERT INTO () statement for your data.

If using 2008 R2 or 2012 it is called something else, see screenshot below this one

alt text

2008 R2 or later eg 2012

Select "Types of Data to Script" which can be "Data Only", "Schema and Data" or "Schema Only" - the default).

enter image description here

And then there's a "SSMS Addin" Package on Codeplex (including source) which promises pretty much the same functionality and a few more (like quick find etc.)

alt text

Related questions

Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run update command ELSE run insert command What's the …

Read More
SQL Server: Is it possible to insert into two tables at the same time?

My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record. I want to copy the data from …

Read More