Azure DevOps, Scrum, & .NET Software Leadership and Consulting Services

Free course! Predicting the Future, Estimating, and Running Your Projects with Flow Metrics

Manage lookup data using SQL Server Data Tools (SSDT)


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.

Lookup table design

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.

img_5663055363790

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.

SUBSCRIBE TO THE BLOG


2 responses to “Manage lookup data using SQL Server Data Tools (SSDT)”

  1. Johnson Avatar
    Johnson

    Fantastic post! This is so chock full of users information and the resources you provided was helpful to me. There I was reading someones’s blog from there i got to know about importance of using lookup table and also basically what is look up table. You may also go through the following link:
    http://www.sqlmvp.org/lookup-table-in-sql-server/

  2. Craig Boland Avatar

    Elegant solution, especially when working with many reference table rows.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.