1

I have a SQL Server 2008 db. It contains some great test data...a proposal that has a lot of child records. I want to script this Proposal so as not to re-enter the data for each new environment. Is there a way to script the data in the proposal as well as all the dependent tables. I can run a query to get the data but what I really need is a set of SQL Insert Statements to insert the data into the new environments. At least that's what I think I need. Any ideas?

Improve this question

2 Answers 2

Reset to default
4

Several options for this exist.

If you're a DIY person and want a manual option with no support, check out Vyas' script:

http://vyaskn.tripod.com/code.htm#inserts

CodeProject article:

http://www.codeproject.com/Articles/5598/Generating-INSERT-statements-in-SQL-Server

Red Gate SQL Compare (to build the schema in the destination) and Data Compare (to pump data over there after the tables are created):

http://www.red-gate.com/products/sql-development/sql-compare/

SSMS Tools Pack:

http://ssmstoolspack.com/Features

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@Aaron...thnaks. I'm sure I can get one of those to work for me. The SQL Server box is not in my control, is the SSMS tool pack a piece of sowftware I'd need installed on the SQL Server box or my local machine?
It's an add-in for SSMS and would be installed on your local machine. If you can query the data, you can generate inserts.
2

If you dont want to use 3rd party stuff, you can pretty much achieve this with SSMS out of the box, simply right click the database, hit "Generate Scripts", follow the wizard, on the options step, set "Script Data" to true (or, if your using 2012, set "Types of data to script" to "Schema and Data").

http://msdn.microsoft.com/en-us/library/ms178078.aspx

http://msdn.microsoft.com/en-us/library/ee210523.aspx

Improve this answer

4 Comments

@Heaven...thanks. I was hoping to be able to target my data to script. In other words, not script all the data, just a particular Proposal and its dependent objects
Ah, ive done similar things in the past, often the easiest thing to do is duplicate the database, delete the stuff you dont want, then script, all depends on the size & complexity of your database i suppose. Do any of the tools lists by Aaron allow you to script sub-chunks of data?
@heaven....I haven't looked at all of the options, but so far I haven't found anything in A's suggestions that script sub-chunks. I'm hopeful though :)
That's a good tool. I just wish it wasn't buried so deep in SSMS. I'd like to be able to right click on a table and choose: Script data.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.