Publishing Your Data Scripts
Tue, Aug 6 2013 8:14
An earlier post detailed how to publish the schema and stored procedure scripts in your SQL Server Database Project. Two other recent posts (one using Insert statements and the other the Merge command) covered how to add a reference data script to your database project. But just adding the reference data script to the database project won't include it in the publishing process.
See this link for an introduction to the 2012 SQL Server Database Projects.
So how do you include your reference data scripts in the publishing process?
1) Create a new script in the same directory as your data scripts.
Right-click on the folder containing your data scripts and select Add| Script.
2) Select a Post-Deployment Script
Visual Studio 2012 comes with several script templates:
Pick the Post-Deployment Script to ensure that the data is populated after all of the tables and stored procedure scripts are executed.
The generated script appears as follows:
3) Include each data script in the post-deployment script.
Notice that the entered command shows syntax errors. That is because the suggested syntax for the post-deployment script is SQLCMD syntax. When using SQLCMD syntax, you need to let the editor know by turning on SQLCMD mode:
Include each data script in this post-deployment script in an appropriate order. If a data script contains foreign keys to data in another script, the other script must be before it in the list.
For example, say you want to populate the Customer table with a sample customer row. The Customer table has a foreign key to the Customer Type table. So populating the Customer table must be *after* populating the Customer Type table. Otherwise the foreign key relationship will fail.
In this example post-deployment script, the Customer data script must then be included *after* the Customer Type data script.
Use this technique any time you want to include data scripts when publishing your database project.
For more information on this and other Visual Studio 2012 features, see my Pluralsight course: Mastering Visual Studio 2012.