Visual Studio SQL Server Database Project – Lookup Data

Previously, the basics of Visual Studio SQL Server Database projects were covered.

Lookup tables are defined and populated early in development to support other developers. The IDs and name ( or short description) are often the essential components that may be used for functionality at a different layer in the solution (UI, business logic, ETL processing, etc.), which requires the data to move as-is from environment to environment. For this post, the Northwinds database is used to show examples.

Adding this data to the project is easy with a few steps, resulting in a post-deployment script.

If you use Red Gate’s SQL Prompt in SQL Server Management Studio, write a SELECT statement to view the records. In the results pane, choose the rows, right-click, and select Script as Insert. As shown below, a new window will open with the records inserted into a temporary table. Feel free to tweak the table name to something more appropriate for the source table, like #categories.

CREATE TABLE #temptable( [CategoryID] int, [CategoryName] nvarchar(15), [Description] ntext )

INSERT INTO #temptable([CategoryID], [CategoryName], [Description])
VALUES
( 1, N'Beverages', N'Soft drinks, coffees, teas, beers, and ales' ),
( 2, N'Condiments', N'Sweet and savory sauces, relishes, spreads, and seasonings' ),
( 3, N'Confections', N'Desserts, candies, and sweet breads' ),
( 4, N'Dairy Products', N'Cheeses' ),
( 5, N'Grains/Cereals', N'Breads, crackers, pasta, and cereal' ),
( 6, N'Meat/Poultry', N'Prepared meats' ),
( 7, N'Produce', N'Dried fruit and bean curd' ),
( 8, N'Seafood', N'Seaweed and fish' )

Otherwise, adding the lookup data can still be tedious but doable. Writing the inserts “long-hand” is one option. Another is to script out the records like this, based on dbo.Categories. The T-SQL function, CONCAT, joins the strings together (after converting the integer CategoryID to a string).

SELECT CONCAT('(', CONVERT(VARCHAR(10), CategoryID), ', ''', CategoryName, ''', ''', Description, '''),')
FROM dbo.Categories;

In another window, define a temporary table to match the schema of the lookup table. Then, start an INSERT statement to add records to the table. Run the query, copy the results to the clipboard, and paste them below the INSERT statement.

The CONCAT function adds a comma to the end of each record. Remove it from the last record to prevent a syntax error at runtime.

CREATE TABLE #categories ( [CategoryID] int, [CategoryName] nvarchar(15), [Description] ntext )
INSERT INTO #categories (CategoryID, CategoryName, Description)
(1, 'Beverages', 'Soft drinks, coffees, teas, beers, and ales'),
(2, 'Condiments', 'Sweet and savory sauces, relishes, spreads, and seasonings'),
(3, 'Confections', 'Desserts, candies, and sweet breads'),
(4, 'Dairy Products', 'Cheeses'),
(5, 'Grains/Cereals', 'Breads, crackers, pasta, and cereal'),
(6, 'Meat/Poultry', 'Prepared meats'),
(7, 'Produce', 'Dried fruit and bean curd'),
(8, 'Seafood', 'Seaweed and fish')

With the temporary table created and populated, write a MERGE statement to insert or update records in the destination table. In this example, dbo.Categories has an identity field, CategoryID, which requires setting INSERT_IDENTITY to ON.

SET IDENTITY_INSERT dbo.Categories ON

MERGE dbo.Categories AS c
USING
(SELECT CategoryID, CategoryName, [Description] FROM #categories) AS src
ON c.CategoryID = src.CategoryID
WHEN MATCHED THEN
    UPDATE SET c.CategoryName = src.CategoryName,
               c.Description = src.Description
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (
        CategoryID,
        CategoryName,
        Description
    )
    VALUES
    (src.CategoryID, src.CategoryName, src.Description);

Bring everything together in a single script that can be copied and pasted into a new T-SQL script in Visual Studio. If a Post-Deployment Script is unavailable in the new file dialog, create a generic SQL file, and in the file’s properties, set the Build Action to PostDeploy. See the highlighted build action in the screenshot below.

During a deployment (or Publish in Visual Studio), the post-deployment script is executed, merging data in the temporary table into the destination table, dbo.Categories. As lookup records evolve over time, manually add, update, or delete records in this script to keep environments in sync.