What is the best way to auto-generate INSERT statements for a SQL Server table?

Question

We are writing a new application, and while testing, we will need a bunch of dummy data. I've added that data by using MS Access to dump excel files into the relevant tables.

Every so often, we want to "refresh" the relevant tables, which means dropping them all, re-creating them, and running a saved MS Access append query.

The first part (dropping & re-creating) is an easy sql script, but the last part makes me cringe. I want a single setup script that has a bunch of INSERTs to regenerate the dummy data.

I have the data in the tables now. What is the best way to automatically generate a big list of INSERT statements from that dataset?

I'm thinking of something like in TOAD (for Oracle) where you can right-click on a grid and click Save As->Insert Statements, and it will just dump a big sql script wherever you want.

The only way I can think of doing it is to save the table to an excel sheet and then write an excel formula to create an INSERT for every row, which is surely not the best way.

I'm using the 2008 Management Studio to connect to a SQL Server 2005 database.

Answer 1

why not just backup the data before your work with it, then restore when you want it to be refreshed?

if you must generate inserts try: http://vyaskn.tripod.com/code.htm#inserts

Answer 2

We use this stored procedure - it allows you to target specific tables, and use where clauses. You can find the text here.

For example, it lets you do this: To generate INSERT statements for table 'titles':

EXEC sp_generate_inserts 'titles'

Answer 3

There is a script to generate INSERT statements from your data here:

Script Tbl Data to Insert Stmts. - Handles Unicode

Answer 4

Don't use inserts, use BCP

Answer 5

Not sure, if I understand your question correctly.

If you have data in MS-Access, which you want to move it to SQL Server - you could use DTS.
And, I guess you could use SQL profiler to see all the INSERT statements going by, I suppose.

Answer 6

Do you have data in a production database yet? If so, you could setup a period refresh of the data via DTS. We do ours weekly on the weekends and it is very nice to have clean, real data every week for our testing.

If you don't have production yet, then you should create a database that is they want you want it (fresh). Then, duplicate that database and use that newly created database as your test environment. When you want the clean version, simply duplicate your clean one again and Bob's your uncle.

Answer 7

Perhaps you can try the SQL Server Publishing Wizard http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

It has a wizard that helps you script insert statements.

Answer 8

You can use SSMS Tools Pack (available for SQL Server 2005 and 2008). It comes with a feature for generating insert statements.

http://www.ssmstoolspack.com/

Answer 9

I have also researched lot on this, but I could not get the concrete solution for this. Currently the approach I follow is copy the contents in excel from SQL Server Managment studio and then import the data into Oracle-TOAD and then generate the insert statements

Answer 10

Jane Dallaway's stored procedure: http://jane.dallaway.com/downloads/SQL/spu_generateInsert.sql. Documentation is a series of blog posts: http://jane.dallaway.com/blog/labels/spu_generateinsert.html

Answer 11

I use sqlite to do this. I find it very, very useful for creating scratch/test databases.

sqlite3 foo.sqlite .dump > foo_as_a_bunch_of_inserts.sql

Answer 12

Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

This is a quick run through to generate Insert statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

1. DATABASE NAME: Right Click
2. TASKS: GENERATE SCRIPTS
3. Under Table/View Options: Set SCRIPT DATA = TRUE

You will then get the create table statement and all of the INSERT statements for the data straight out of SSMS.

Answer 13

This one so far has worked for me, although they might be bugs I have not discovered yet .