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.

Lussier

Answer 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.

Carruthers

Wow, that add-in looks great, I hope that it works also with Express Edition. – Darius

As of SQLSMS 2012, the tool is no longer free, although it offers a 30 days time trial. See mobill's answer, there's a built-in way to achieve this. – Fenske

Answer 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'

Maribeth

I don't know this isn't the top answer. Maybe because it doesn't apply to SQL Server 2000. But this is exactly what I was looking for. – Ceyx

I've switched to SSMS 2012, and for some reason the script doesn't include printing "processes xxx records" anymore. How do I get it back? – Tonatonal

@Tonatonal Don't know, don't have 2012. I spent some time trying to get rid of those print statements back when I posted that.... – Maribeth

Answer 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).

Rescissory

Answer 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).

Darius