Populating Lookup Tables in SQL Server DataTools (SSDT)

Lookup data never really felt like data to me

Definitions

Lookup Table: A table with 2 fields at a minimum, a meaningless primary key and a text value used in normalization to avoid repeating textual values and referenced by foreign keys in other tables. I’ve also heard them called Reference Tables, and Domain Tables. These tables are not changed during the normal course of business (unlike something like ProductCategory which looks the same but I’d argue isn’t a lookup table). This is my definition so don’t take this as gospel.

State table could go either way. That could be maintained by your application.
SQL Server Data Tools — Table Editor UI

Is Lookup Table Data Really Data?

Think of the corresponding code. It’s usually an enum, right? Sometimes you’ll implement this as class and load lookup data from the database to avoid hard coding this twice but you’re looking for enum-like functionality. Here are a few more tests…

  • Would a “blank” copy of the database be complete without this data?
  • Do they change without a corresponding code push?

Source controlling lookup table data in SSDT

The beauty of SSDT is that we create a declarative model of what a target database should become after a deployment regardless of its current state. We want lookup table data to be treated the same way both in source control and during deployment.

Example

The lookup table
LookupTable.UserStatus.sql

A couple things you may notice…

Lines 5–10 represent the data that is the desired final state of the lookup table

Deployment

We want to deploy these using a post deployment script. Since you can only have one, I use the following setup:

Script.PostDeployment.sql
Solution Explorer
Types of Scripts

Caveats

  • This will fail in cases where you end up deleting lookup table rows that are referenced by a foreign key in another table. This may happen during a rollback scenario and will require you to disable foreign keys or manually intervene prior to running the scripts unless you have cascading deletes turned on (which I wouldn’t do).
  • You’ll also have failures when two teams are using the same DB in dev and deploying from their branches without merging them first.

Thoughts?

How do you do it? Does anyone out there have a better way?

I'm a software architect, data engineer, surfer, and musician who loves problem solving and interesting tech.