While you may use the online method to link and automatically import changes to static data tables, there may be some cases where you prefer to manage static data in Post-Deployment scripts. This methodology is suited to smaller static data tables where you require additional flexibility, such as customized static data per environment. For larger static data tables, the data seed method with BULK INSERT may be appropriate.

Notable points to remember about pre-deployment and post-deployment scripts

• Pre and post deployment scripts must be re-runnable as they will be executed as part of every deployment. 
• Pre and post deployment scripts are deployed to every environment
• You need to provide your own error handling as desired in pre and post deployment scripts - the example below uses a TRY/CATCH pattern
• See the main page regarding ordering, transaction management, and more

Post-Deployment script to add environment specific static data

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

In this example, we use a post-deployment script to check @@SERVERNAME during the deployment and customize the static data in the table based on the result.

Please note:

• We USE [$(DatabaseName)] to ensure we are working with the correct database (not the default database for the login).
• This example utilizes a merge statement.  See documentation on the offline static data method for limitations of merge.
• This example assumes that a table named dbo.MarketingReasons already exists in the development database as well as in the project.
  • The table definition could be present in the baseline of the database or a in incremental migration script.
  • For testing purposes, this table can be created with the script: CREATE TABLE dbo.MarketingReasons (ID INT, ReasonType VARCHAR(MAX));

/*
Post-Deployment Script Example
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be added to build and deployment scripts
 This example requires SQLCMD syntax
 For more information, see https://documentation.red-gate.com/display/sca4/Pre-deployment+and+post-deployment+scripts
--------------------------------------------------------------------------------------
*/

SET XACT_ABORT, NOCOUNT ON;
GO
USE [$(DatabaseName)];
GO

BEGIN TRY

    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;

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