What is the best way to auto-generate INSERT state

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?

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

I used this script which I have put on my blog (How-to generate Insert statement procedures on sql server).

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