Sometimes you need to be able to script the data out of a table and it is easier than you might think.

I commonly do this when I am generating automated tests or updating a product with system data for deployment.

In a future post I will go through how I use this in conjunction with our product testing suites

For this example, let’s assume I am scripting out some system configuration values for a current product to ship with the release.

Here’s how:

1. Open SSMS (SQL Server Management Studio)
2. Right Click on the Database that contains your data and select Tasks followed by Generate Scripts

3. Click Next on the Wizard Introduction Page
4. On the Choose objects you can select the objects you would like to script out of the database. We will select “Select specific database objects” and then under tables select the table we are interested in scripting data from – [CONFIG].[GeneralSettings]
5. 
6. Click Next
7. Now we will select our scripting options. By default you would normally just get the object creation script, but this is where we can specify we want the data. Click on the “Advanced” button to bring up the Advanced Scripting Options dialogue.

8. At the very bottom of the General section we want to change the “Types of data to script” from “Schema Only”. Normally, I would probably just select “Data Only”, but for this example I will choose “Schema and Data” so you can see what both provide.

9. Click “OK” to return the Script Options page.
10. Now select how you want to output the script. If it is a relatively small amount of data (hundreds of rows or less) then I usually choose “Save to new query window”. You could also choose “Save to clipboard” and then paste it where you want. If you are dealing with a large amount of data (thousands of rows or more) you may want to leave the “Save to file” option selected. For our example choose “Save to new query window”

11. Click Next on the Scripting Options page
12. Click Next on the Summary page
13. You will get a confirmation page similar to the following.

14. You should have gotten a New Query in management studio that contains the requested results.

I can now copy the data inserts and use them as defaults or automated testing data, etc.

Another Tip: if you are actually scripting the objects and want to do perform an existence check change the “Include IF NOT EXISTS” flag

Do you have any interesting uses for this?

Let us know in the comments below.