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

sql sql-server tsql sql-server-2000 data-export
Justin808 picture Justin808 · Nov 3, 2010 · Viewed 25k times · Source

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.

Answer

mobill picture mobill · Mar 5, 2014

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:

enter image description here

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'

Related questions

How to Join to first row

I'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098FBE3...} 1 7 prefabulated amulite {1609B09...} 2 32 spurving bearing But occasionally there …

Read More
How to find column names for all tables in all databases in SQL Server

I want to find all column names in all tables in all databases. Is there a query that can do that for me? The database is Microsoft SQL Server 2000.

Read More
How to increment in a select query

I've got a query I'm working on and I want to increment one of the fields and restart the counter when a key value is different. I know this code doesn't work. Programmatically this is what I want... declare @counter …

Read More