Redgate Flyway

Flyway also supports Repeatable Migrations that will execute on the target environment after all the pending migration scripts run every time the contents in the Repeatable Migrations change. You can write an idempotent script to manage data or use MERGE if your database supports that.

SQL Server Merge Example:

R__Data-MarketingReasons.sql

/*  Manage Marketing Data  */
 
SET XACT_ABORT, NOCOUNT ON;
GO
 
BEGIN TRY 
    CREATE TABLE #MarketingReasons (ID INT, ReasonType VARCHAR(MAX));
    INSERT INTO #MarketingReasons
    VALUES(1, 'Recommendation'),
        (2, 'Review'),
        (3, 'Internet');

    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

If you change this file to add an additional value into the temporary table, then the next time flyway migrate is executed, this script will be included in the migration.

SQL Server - Generate Merge Statement:

You can use the sp_generate_merge stored procedure utility to generate MERGE statements.

Download the the "sp_generate_merge" stored procedure from GitHub . Run it on your development SQL Server instance. This will install the utility as a system procedure within the [master] database so that it can be used within all of your user databases.
In SSMS, connect to your development database and ensure that your results are sent to grid, rather than text.
Execute the stored procedure, providing the source table name as a parameter. If your table is in a non-default schema, be sure to supply the @schema parameter with its name.
For example:
```
EXEC AdventureWorks.dbo.sp_generate_merge 'Product', @schema='SalesLT'
```
Click the hyperlink in the result set to open up the Xml fragment in a new document window.
Delete the XML tags, then copy the document contents to the clipboard and paste it into your Repeatable Migration script.

Limitations of Merge

Please be aware of the drawbacks to using merge:

Non-determinism of the MERGE statement: before actually running the deployment against your target environment, it can be difficult to know what changes will be applied (if any). Worst case scenario, you could hit one of the documented issues in MERGE.
The workflow involves editing the Repeatable Migrations directly or alternatively using the utility procedure and copying + pasting the output back into the original file.
You have to coordinate changes to both the schema and data within the reference table, given that Versioned Migrations run before Repeatable Migrations. For example, if you need to update an ID value in the lookup table prior to adding a foreign key to the schema, then that update would need to be manually included in the Versioned Migration.

Learn more about Repeatable Migrations.

Read an example of using PowerShell to Bulk Load Data in SQL Server.

Redgate Flyway

Using Repeatable Migrations to manage data

SQL Server Merge Example:

SQL Server - Generate Merge Statement:

Limitations of Merge