How do I export a table's data into INSERT statements?

Question

How can I export a table from a SQL Server 2000 database to a .sql file as a bunch of INSERT INTO statements?

One of the fields in the table is a Text datatype and holds HTML so doing this by hand would be rather time-consuming.

I have access to SQL Server Management Studio 2008 to access the SQL Server 2000 database.

Solution 1

Check out the SSMS Tool Pack - it's a great, FREE add-on for SQL Server Management Studio which does a lot of things - among other it can generate INSERT statements from a given table.

Solution 2

Updating since this Q&A was at the top of the search results when I was looking for the answer.

In MSSQL 2008 R2:

Right Click on database: Tasks -> Generate Scripts...

The Generate and Publish Scripts dialog will pop up. The Intro page is worthless. Click "Next"

Choose "Select Specific database objects" and then select the Table(s) you want to get Inserts for. Click Next and the dialog will advance to the "Set Scripting Options".

Click on Advanced and you should see:

Scroll down the list of Options until you find "Types of data to script". Click on that row and choose "Data Only" from the pull-down. Click "OK". Choose your Save options and click "Next" a few times.

Note - The output also includes the following after every 100 inserts.

  GO
  print 'Processed 200 total records'

Solution 3

I have been using this stored procedure for a long time: sp_generate_inserts: the 2000 version and the 2005 (and up) version.

You use it like this:

sp_generate_inserts 'thetablename'

or if you want to filter:

sp_generate_inserts 'thetablename', @from='from ... where ... order by ...'

The sp will return inserts statements as query results. Don't forget to modify setting: increase the maximum number of characters displayed in each column (tools - options - query results).

Solution 4

If you can use other DB management apps the quickest way would be using a tool like SqlDbx which has a built-in "Export as inserts (SQL)" function (just execute a query like SELECT * FROM Table and then use the contextual menu from the result grid).

If you need to stick to SQL Management Studio then you could use a stored procedure like this one:

http://vyaskn.tripod.com/code/generate_inserts.txt

It generates a set of results with the SQL INSERT statement for each row of the target table. Then you can exports the results to a file, or just copy them to the clipboard and paste in the query window (it works fine even with several megabytes of data).

View more solutions