Scripting Reference Data with SQL Server Object Explorer
Mon, Aug 5 2013 8:31
Database projects are great for scripting the tables and stored procedures for a database, but what about data? There are often times that you need to pre-populate tables with reference data.
See this link for an introduction to SQL Server Object Explorer.
See this link for an introduction to the 2012 SQL Server Database Projects.
For example, you may have a type table that contains the set of valid types. In this case, we have a CustomerType table. We want to retain the valid set of data for that table in a data script.
To script the data from a table into a database project.
1) Create a folder in the database project for your data scripts.
Right-click on the Database Project and select Add | New Folder. I named my folder: "Reference Data"
This step is optional, but it helps to keep all of your data scripts together in one place.
2) Create a script for the new data.
Right-click on the new folder and select Add | Script. Select the "Script (not in build)" template. I name my data scripts the name of the table.data.sql.
Visual Studio then opens a nice empty window for you to type in all of your data. Not Fun!
3) Instead of typing in all of your data, script the data from the SQL Server Object Explorer.
Open SQL Server Object Explorer, right-click on the table, and select "View Data".
The table data is then displayed:
4) Select the Script icon (see red square in the above diagram).
The script to create the data is generated:
5) Copy the data and paste it into the script you defined in your database project.
NOTE: There are several different ways to accomplish this same task.
- Instead of creating the database project script, generating the data script, and copying the contents of the generated data script into the database project script, you can just generate the data script and save it into your project.
- Alternatively, you can click the Script to File icon to the right of the Script icon to generate the script to a specific script file.
6) The final step is to add a Delete statement to delete any existing rows.
This ensures that no rows existing before attempting to insert the pre-defined rows:
Use one of these techniques to script any reference data into your database project.
For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.