Is there a tool that allows me to create a sql populate script for a table using its current data?
Add a comment
|
6 Answers
Old question, but I got to the question before figuring out a way to solve the problem. So I'm just posting the method here in case it might help anyone else.
In SSMS:
- Right click on db -> "Tasks" -> "Generate Scripts..."
- Click "Next" and, at "Choose Objects" step, select the specific table
- Click "Next" and, at "Set Scription Options" step, click "Advanced"
- Set value for "Type of data to script", depending on what you need
- Continue to finish
1 Comment
Flydog57
It's worth noting that this is almost the right answer. If you want to script out the INSERT statements needed to repopulate the database, you need to find the Types of data to script (the last entry in the first block of Advanced Options) as @Gustav mentions and pick Schema and data. That's buried just about as far down in the muck as possible.
Check out the SSMS Addins (for both SSMS 2005 and 2008):
One of the tools included in the "DataScripter" which will script out INSERT statements for the table(s) you want to.
Comments
Maybe this is just enough for you: http://snipplr.com/view/12003/generate-insert-statements/
Comments
Another way to do it, even more convenient for developers, is within Visual Studio 2015 or 2017 (possibly earlier versions too):
- Main menu 'View' -> 'SQL Server Object Explorer'
- In the tree view connect to the Database of choice and select a table
- Right click on the table and select 'View Data' menu item
- In the [Data] window click on a 'Script' or 'Script to File' button
1 Comment
Michael Edmison
Visual Studio 2022 is almost identical, but for step for has an update here: 1. Main menu 'View' -> 'SQL Server Object Explorer' 2. In the tree view connect to the Database of choice and select a table 3. Right click on the table and select 'View Data' menu item 4. In the [SQL] menu, click on a 'Script' or 'Script to File' button
You could look at purchasing the Data Generator by Red-Gate which can create random sets of data based on your database schema.
Note: I'm not sure if the tool will generate data based on your current data though
1 Comment
marc_s
No, it won't - it's a test data generator which will create new test data for your tables, based on some data repositories. But it definitely does not script out your current data in your tables.
You can do it with Database Publishing Wizard. Here is a tutorial: Generate Script with Data from Database – Database Publishing Wizard
