Redgate Flyway

Tutorial - Use repeatable migrations to manage data

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.

  1. 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.  
  2. In SSMS, connect to your development database and ensure that your results are sent to grid, rather than text.
  3. 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'
  4. Click the hyperlink in the result set to open up the Xml fragment in a new document window.

  5. Delete the XML tags, then copy the document contents to the clipboard and paste it into your Repeatable Migration script.



Didn't find what you were looking for?