3

I'm setting up a test environment for development and need to export some static data from tables in Production. Ideally the exported product would take the form of a script with all the required INSERT statements. In doing so, I can reset the development environment quickly after each test.

Can anyone tell how to accomplish this using SQL Server 2008, SQL Management Studio, and/or Visual Studio 2010?

Note: None of the tables have any foreign keys, I have full permissions in the source database, and the number of rows per table is no more than a few dozen.

8

As well as using SSMS, you can use the commercial SSMS Tools Pack

I prefer it: more options, batching, cleverer all round.

Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and 10 Mb. Larger values than the limit are then scripted as NULL. Insert statements for the data in result grids are scripted into a new temporary table for each grid. For example from 5 result grids insert statements for 5 temporary tables get created.

  • 1
    @gdn Thanks, this worked a treat. I exported the scripts and put them in my VS 2010 SQL Server Database Project and now all my static data is under source control and is part of the database deployment package. Sweet! – JoeGeeky Jan 24 '12 at 19:51
  • 2
    This is actually built into SSMS 2008. Right click the database, go to task, then generate scripts. In the options set Script Data to true. There is also another SSMS add in that makes it easier to get to: ssmsaddins.codeplex.com – m4tt1mus Feb 17 '12 at 23:07
6

Use the standard SSMS scripting feature:

Right click on database in object explorer->Tasks->Generate Scripts

Then choose what tables do you want to script and not forget to check "Script Data"

0

Try www.sqlscripter.com

It supports INSERT statements as well as UPDATE etc., so you can do something like this: IF NOT EXISTS (...) INSERT ... ELSE UPDATE ...

It works for us so it may work for you.

protected by Community Apr 24 '17 at 14:56

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.