So let's say that you've managed to get your SQL Server database schemas -- your tables, stored procedures, views, functions, etc -- under source control using SQL Server Data Tools (SSDT) in Visual Studio.  Great.  That's gotten you really far along towards having nice automated deployments and good DevOps practices for your application (because you can't do an application deploy without the app's database, right?).

Your next problem is how to manage "lookup" or "reference" data.  What do I mean by "lookup" data?  Well, you know those lists of values that get used by your application?  Maybe the list of states or countries or corporate titles or customer types or phone number types?  All that stuff that probably shows up in a dropdown control somewhere in your application.  Hopefully, you aren't just hard-coding that into your application.  Typically, the best place to put those values is in a database table.

It probably looks something like this.

You've got an int Identity column, a LookupType column that describes what kind of value this is ("phone type", "country", "employee title", etc.), and a LookupValue column that contains the actual values that would show up in that drop down control ("Home Phone", "Mobile", "Work Phone", etc.).

Well, just like your application won't work without its database, the application probably won't work all that well without the lookup values that drive the screens.

SSDT Post Deploy Scripts

SQL Server Data Tools (SSDT) allow you to manage this lookup data by using a type of script called a Post Deployment Script.

A Post Deploy Script in an SSDT project is just a plain old regular *.sql file -- but there can only be one of them for that project.  You'll put the data for your lookup table into this script and then whenever you publish of your database project to SQL Server, this data will get updated in the database table.

T-SQL 'MERGE INTO'

So far I'm making this seem like the SSDT Post Deploy is the magic that makes this all happen.  But really, SSDT only does about half that work.  The other half of the work is done using the T-SQL MERGE INTO statement.  MERGE INTO lets you define a list of values and has you define a related set of INSERT, UPDATE, and DELETE statements that will be run in order to make the database table look like the data supplied to the core of the MERGE INTO statement.

Here's a sample MERGE INTO that I use to populate my Lookup table with values for 'phone-type' and 'employee-title'.

MERGE INTO Lookup AS Target USING (VALUES (0, 'phone-type', 'home'), (1, 'phone-type', 'work'), (2, 'phone-type', 'mobile'), (3, 'employee-title', 'ceo'), (4, 'employee-title', 'cto'), (5, 'employee-title', 'cio'), (6, 'employee-title', 'vice president'), (7, 'employee-title', 'director'), (8, 'employee-title', 'chief chef'), (9, 'employee-title', 'janitor') ) AS Source (Id, LookupType, LookupValue) ON Target.Id = Source.Id WHEN MATCHED THEN UPDATE SET LookupType = Source.LookupType, LookupValue = Source.LookupValue WHEN NOT MATCHED BY TARGET THEN INSERT (Id, LookupType, LookupValue) VALUES (Id, LookupType, LookupValue) WHEN NOT MATCHED BY SOURCE THEN DELETE;

The "USING VALUES" section defines the values that I want to ultimately see in that table.  The WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE sections of the command define what to do when those cases occur.  In this case, it does an INSERT, UPDATE, and DELETE respectively.

Summary

So that's the way you can use SSDT to manage lookup values (aka. manage data) as part of your source controlled database schema.  First step, add a post deploy script to the database project.  Second step, use T-SQL's MERGE INTO command to define the list of values.

Here's some sample code that show you how to do this.

I hope this helps.

-Ben

-- Having trouble getting your database code into Team Foundation Server?  Want to manage your database code with Git?  Trying to include SQL Server databases in your DevOps flow?  We can help.  Drop us a line at info@benday.com.