I’m a fan of the SQL Server project type that ships with Visual Studio having used it over the years with a number of projects.  I’ve been away from it for a couple of years though having been working with a company that preferred Redgate’s SQL Source Control offering, so coming back to SQL Server projects again my memory’s been a bit hazy and I’ve struggled how to remember to do some things.

One of these things was how to manage static data (or default data) that I want to ship with my database when I publish it.  The solution is to use a post deployment script.

Post Deployment Script

A post deployment script gets executed after the main deployment script and within it it’s possible to insert data that should exist in your database post deployment.  This might be useful for populating a Title table in a Human Resources database with Mr, Mrs, Ms, Dr etc, or, as in the example below, populating a table with a list of cars:

Adding a Post Deployment Script to your SQL Server Project

To add a post deployment script to your SQL Server Project in Visual Studio, right click on your project, select Add and then Script.

From the resultant popup, click on Post-Deployment Script, give it a name (I’d suggest keeping the PostDeployment wording in the name), and then click Add.

This will add the script into your solution which can be seen in the Solution Explorer window

Okay, with the plumbing done it’s time to write the script.

The Script

The below script is one I quite like as it manages INSERTS, UPDATES, and DELETES all in one go.  The MERGE INTO statement was introduced in SQL Server 2008.  More information on it can be found here.

— Allow IDENTITY insert for table Cars
SET IDENTITY_INSERT Cars ON
GO

— Reference Data for Cars
MERGE INTO Cars AS Target
USING (VALUES
(1, N’Ford’),
(2, N’Audi’),
(3, N’BMW’),
(4, N’Volkswagen’),
(5, N’Fiat’),
)
AS Source (Id, CarMake)
ON Target.Id = Source.Id
— update matched rows
WHEN MATCHED THEN
UPDATE SET CarMake = Source.CarMake
— insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, CarMake)
VALUES (Id, CarMake)
— delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN DELETE;
GO

SET IDENTITY_INSERT Cars OFF
GO

Summary

So there you go, we now have a nice way of releasing data we want to go with our database at the time it is deployed.  This is not only useful for static data but can be used to populate a non production database with test data.  Plus as it can be added to source control along with the rest of your solution you can make this script available to the rest of your development team.