Deploying Lookup Data in Dacpacs

Introduction

Deploying lookup data with a dacpac (SSDT sqlproj) is a relatively straightforward process. SqlServer dacpacs have facilities for running scripts before and after the main deployment executes. Deploying lookup/seed data with a dacpac simply consists of adding the appropriate statements to your Post-Deployment script.

It can be accomplished by following these steps:
1. Add a Post-Deployment script to your project
2. Create a script for each table to be populated
3. Reference the script from the post-deployment script
4. Populate the script with merge statements containing your data

Adding a Post-Deployment Script to Your Project

I like to keep my pre and post deployment scripts in there own folder. To do so, simply right-click the root of the project and select Add->New Folder. Name the new folder “Scripts”.

Next, right-click the Scripts folder and select Add, then way at the bottom Script...

From the list of script types, pick Post-Deployment Script. I usually just call it Script.PostDeployment.sql since you can only have one Pre or Post Deployment script per project.

Once you have added the script, look at the properties. You will notice that its Build Action is set to PostDeploy. Again, you can only have one file with this build action. However, as I will show you, you can use separate files to organize your post deployment.

Create a Script for Each Table

Now create another folder under Scripts called “LookupData”. Finally, right-click the LookupData folder and once again, add a script to you project. This time select the Script (Not in build) type and name it the same name as your table. For example, “CategoryTypes.sql”. It is important that these scripts are not included in the build as they are data-related and not schema-related. In most cases they will cause the build to fail. If you add existing scripts, from elsewhere, be sure to manually set the Build Action property to None.

Reference Your Table Scripts

Open your post deployment script in the editor. At the top, if you read the comments, you will notice that you can use SQLCMD syntax to include addition files into the post-deployment script.

As you will notice, there are red squiggle marks under the : and . characters in the script. This is because Visual Studio does not know this script is using SQLCMD syntax. To let it know and eliminate the marks, click the SQLCMD mode button:

Continue adding tables scripts and referencing them from your Post-Deployment script.

Populate the Script with Merge Statements

The last step, adding Sql Merge statements to the scripts, is the most important.

SQL MERGE statements allow the scripts to be run repeatedly without changing the state of the database (they are idempotent). Merge statements basically synchronize a source and target, adding missing rows, updating existing ones (if needed) and deleting ones that do not exist in the source.

The general syntax is described at MSDN.

An example:

MERGE INTO [CategoryType] AS Target
USING (VALUES
    (1,'Turkey')
    ,(2,'Green')
    ,(3,'Melamine')
    ,(4,'Convertible')
) AS Source ([CategoryTypeID],[Name])
ON (Target.[CategoryTypeID] = Source.[CategoryTypeID])
WHEN MATCHED AND (
NULLIF(Source.[Name], Target.[Name]) IS NOT NULL OR NULLIF(Target.[Name], Source.[Name]) IS NOT NULL) THEN
 UPDATE SET
 [Name] = Source.[Name]
WHEN NOT MATCHED BY TARGET THEN
 INSERT([CategoryTypeID],[Name])
 VALUES(Source.[CategoryTypeID],Source.[Name])
WHEN NOT MATCHED BY SOURCE THEN 
 DELETE;

Of course, you can write these statements from scratch but if you are lazy like me, you will want to generate them. Most likely you have the data in an existing development or production database. There are many tools that can take such data and generate your merge statements. One such example can be found here: https://github.com/readyroll/generate-sql-merge

Keep in mind that in practice you will not likely be able to use the delete clause at will:

WHEN NOT MATCHED BY SOURCE THEN 
 DELETE;

There will likely be historical records that will reference these lookup values. You must either remove the clause or first purge all the data referencing the removed records.

Conclusion

Following these steps you will have all of your lookup and seed data stored in source control, along with your schema. You will get all the benefits of being able to track the history and changes over time and be able to automatically sync your data when deploying your dacpacs.