mssqltips logo

Insert inital seed data into the table in SSDT



By:

Overview

When databases are created or upgraded, data may need to be added, changed, or deleted. Moreover, certain actions may have to occur on the database before and/or after the process completes. Pre and Post Deployment scripts can be used to accomplish this.

Explanation

In our SSDT SQL project, we've defined a new table but we need a way to insert inital seed data into it. This can be accomplished by creating a PostDeploy script. In the Solution Explorer, right click on the Futbol project and choose Add->Script.

Add New Script

You will again then be presented with the Add New Item dialog where you can choose the type of script you wish to create. Here you can choose to create a PreDeploy script, a PostDeploy script, or loose scripts that are checked for syntax/object errors when a build occurs (Script (Build)) or not checked when a build occurs (Script (Not in Build)). Both the pre and post deployment scripts are SQLCMD aware which means you can create separate scripts within the Solution Explorer and include them directly into the main Script.PostDeployment.sql (or Script.PreDeployment.sql) script using the :r command of SQLCMD. This allows you the freedom to isolate specific tasks to specific scripts as opposed to maintaining one monolithic script.

Choose Post-Deployment Script and click Add.

Add Post-Deploy Script

You will then be presented with a script template that you can fill in. Here, I enter a couple of seed data entries and save my script.

Save Post-Deploy Script

Note the conditional logic. Pre and post deployment scripts always run irrespective of whether we're installing or upgrading. It's prudent to make your scripts idempotent (or re-runnable) so a refactor of your database doesn't fail.

When a database publish occurs, 3 blocks of code are created within a central .sql script which is generated by Visual Studio. This final auto generated script is what is ultimately deployed to the database. This script has three main blocks of code produced in this order:

  • All code that makes up Scripts.PreDeployment.sql
  • Auto generated code representing database schema creates/drops/changes based on the contents of the project
  • All code that makes up Scripts.PostDeployment.sql

Additional code may also be added to this main script by Visual Studio depending on what deployment options that were chosen such as constraint checking.

You may be wondering how a pre deployment script could be useful. One situation to consider is a complex migration of data. Using the pre and post deployment scripts, you could perform some up front work prior to deployment occurring and then work on that data after the deployment occurs.

Additional Information


Last Update: 7/22/2014




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools