Post-Deployment script to add environment specific static data

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.
• 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.