How can I automatically create INSERT scripts from existing SQL Server data?

Data and Databases
1

I’m working on a new project and need to set up test data for our application. Right now I’m using MS Access to import Excel spreadsheets into our database tables, but this process is getting annoying.

Whenever we need to reset our test environment, I have to drop all the tables, recreate them, and then run the Access import again. The dropping and recreating part is simple with SQL scripts, but having to rely on Access for the data import is really bothering me.

What I want is one complete setup script that includes all the INSERT commands to populate our tables with the dummy data. Since I already have all this information loaded in the database tables, there must be a good way to generate INSERT statements automatically from the existing records.

The only method I can come up with is exporting everything to Excel and using formulas to build INSERT statements for each row, but that seems like a terrible approach. I’m working with SQL Server Management Studio 2008 connected to a SQL Server 2005 instance. Any suggestions for a better solution?

2

Oh wait, are you using the generate scripts wizard in SSMS? There’s an option to script data too, not just schema. Curious though - how big are your test datasets? And do you need to scrub any sensitive info before generating those inserts?

3

hey! for sure, try using SSMS for that. it’s so much better, plus u can always edit the scripts afterward if u need to. it’ll save u a lot of time, trust me!