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'm using SSMS 2008 version 10.0.5500.0. In this version as part of the Generate Scripts wizard, instead of an Advanced button, there is the screen below. In this case, I wanted just the data inserted and no create statements, so I had to change the two circled properties

Answer 2

As mentioned by @Mike Ritacco but updated for SSMS 2008 R2

1. Right click on the database name
2. Choose Tasks > Generate scripts
3. Depending on your settings the intro page may show or not
4. Choose 'Select specific database objects',
5. Expand the tree view and check the relevant tables
6. Click Next
7. Click Advanced
8. Under General section, choose the appropriate option for 'Types of data to script'
9. Complete the wizard

You will then get all of the INSERT statements for the data straight out of SSMS.

EDIT 2016-10-25 SQL Server 2016/SSMS 13.0.15900.1

1. Right click on the database name

2. Choose Tasks > Generate scripts

3. Depending on your settings the intro page may show or not

4. Choose 'Select specific database objects',

5. Expand the tree view and check the relevant tables

6. Click Next

7. Click Advanced

8. Under General section, choose the appropriate option for 'Types of data to script'

9. Click OK

10. Pick whether you want the output to go to a new query, the clipboard or a file

11. Click Next twice

12. Your script is prepared in accordance with the settings you picked above

13. Click Finish

Answer 3

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 4

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 5

Don't use inserts, use BCP

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.