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!
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.
- Open SSMS and connect to the server where your table is located at.
- Right click on the database where your table is stored, I’m using AdventureWorksDW2012.
- Next click on Tasks
- Next click Generate Scripts
- Inside the Generate Scripts wizard click next on the introduction screen.
- For Choose Objects screen –> Click “Select specific database objects”.
- Expand the tables list.
- Select the DimCurrency Table.
- 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.
- On the same screen select the “Advanced” button found on the right side.
- Now scroll down to “Types of data to script” and change it from schema only to Schema and Data.
- 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.
As always, thanks for looking!