Welcome back to this series on SQL Tips. Sometimes I find myself needing to not only generate the DDL for a table but also the data that is stored in that table. For example imagine I have created and populated a few tables for a blog post. It would be much easier if I could share the table definition and the SQL statement to populate that table, Agree? Great!

Scenario

In this scenario I am going to show you how to generate the DDL to create the DimCurrency table from the AdventureWorksDW2012 database. Along with the DDL script I will also show you how you can generate the script to populate the table.

Walkthrough

1. Open SSMS and connect to the server where your table is located at.
2. Right click on the database where your table is stored, I’m using AdventureWorksDW2012.
3. Next click on Tasks
4. Next click Generate Scripts
5. 
6. Inside the Generate Scripts wizard click next on the introduction screen.
7. For Choose Objects screen –> Click “Select specific database objects”.
8. Expand the tables list.
9. Select the DimCurrency Table.
10. 
11. On the “Scripting Options screen” I’m just going to load the script into a new query window. Please select “Save to new query window”. This option is found at the bottom of the screen.
12. On the same screen select the “Advanced” button found on the right side.
13. Now scroll down to “Types of data to script” and change it from schema only to Schema and Data.
14. 
15. Click Ok. Click Next, Click Next. The script will now be created and generated in a new query window. Click Finish once it has completed. Below is a partial screenshot of the completed product.
16. 

As always, thanks for looking!