While you may link static data tables to source control data which doesn't change very often, there may be some cases where you prefer to manage the static data in Post-Deployment scripts.

Post-Deployment script to add environment specific static data

A post deployment script allows you more flexibility with static data tables.

In this example, the script checks @@SERVERNAME for the database during the deployment and adds a value for a specific environment.

CREATE TABLE #MarketingReasons (ID int, ReasonType varchar(MAX))
INSERT INTO #MarketingReasons VALUES
        (1, 'Recommendation'),
        (2, 'Review'),
        (3, 'Internet')
IF (@@SERVERNAME = 'DEV-DEVELOPERNAME')
BEGIN
	INSERT INTO #MarketingReasons VALUES
        (4, 'Dev recommendation')
END

MERGE INTO dbo.MarketingReasons AS target
USING #MarketingReasons AS source
ON target.ID = source.ID
WHEN MATCHED THEN
    UPDATE SET ReasonType = source.ReasonType
WHEN NOT MATCHED BY TARGET THEN
    INSERT ( ID, ReasonType )
    VALUES ( ID, ReasonType )
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;
GO

IF @@ERROR <> 0 SET NOEXEC ON
GO

Notable points:

• Post-Deployment scripts must be re-runnable as they will be executed as part of every deployment
  • A MERGE statement is used to copy static data into the dbo.MarketingReasons table (more on merge statements below)
• Post-Deployment scripts are deployed to every environment
  • @@SERVERNAME is used to add an extra row in the development database.
• By default there is no error handling, so this script tests for an error, and sets NOEXEC to ON if an error occurs

Post-Deployment script to manage static data for a table with an identity column

There may be times when you wish to control the exact script for static data tables, rather than using syntax automatically generated by SQL Source Control.

Teams who use branches for feature development may prefer this method for static data tables which use identity columns, for example, as this approach tends to create a more readable merge scenario than the "link static data tables" feature.

SET XACT_ABORT, NOCOUNT ON;
GO

BEGIN TRY
    /* If the table has an identity value, we need to manage that*/
    SET IDENTITY_INSERT dbo.Shippers ON;

    BEGIN TRANSACTION;
    MERGE INTO dbo.Shippers AS target
    USING
    (
        VALUES
            (1, N'Speedy Express', N'(503) 555-9831'),
            (2, N'United Package', N'(503) 555-3199'),
            (3, N'Federal Shipping', N'(503) 555-9931'),
            (4, N'International Shipping', N'(503) 555-5930'),
            (5, N'Universal Shipping', N'(503) 555-9936'),
            (6, N'Galactic Shipping', N'(503) 554-9936')
    ) AS source (ShipperID, CompanyName, Phone)
    ON target.ShipperID = source.ShipperID
    WHEN MATCHED THEN
        UPDATE SET target.CompanyName = source.CompanyName,
                   target.Phone = source.Phone
    WHEN NOT MATCHED BY TARGET THEN
        INSERT
        (
            ShipperID,
            CompanyName,
            Phone
        )
        VALUES
        (source.ShipperID, source.CompanyName, source.Phone)
    WHEN NOT MATCHED BY SOURCE THEN
        DELETE;

    SET IDENTITY_INSERT dbo.Shippers OFF;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@trancount > 0
        ROLLBACK TRANSACTION;
    DECLARE @msg NVARCHAR(2048) = ERROR_MESSAGE();
    RAISERROR(@msg, 16, 1);

    SET NOEXEC ON;
END CATCH;

Notable points for this script:

• Post-Deployment scripts must be re-runnable as they will be executed as part of every deployment
  • A MERGE statement is used to maintain static data in dbo.Shippers table (more on merge statements below)
• Post-Deployment scripts are deployed to every environment
• By default there is no built in error-handling for post-deployment scripts. In this script we have used a TRY/CATCH pattern based on Erland Sommarskog's excellent error handling reference.
• This sample uses a table value constructor, which is available in SQL Server 2008+.
• This code sample uses the Northwind database schema. Northwind is shared by Microsoft Corporation under the MIT License.

A note about merge statements

Merge statements don't always scale well to large datasets or complex scenarios in SQL Server. 

We do find that merge statements are popular with developers for static data table approaches in post-deployment scripts, however, as this use case generally involves updates to relatively small tables. Generally these tables are updated only in database code deployments by a single process, so concurrent updates are generally not a concern.

Not everyone enjoys the syntax for merge. A free helper procedure, sp_generate_merge, is available if you would like to auto-generate merge statements to help in managing static data.