Best practices for migrating SQL Server MERGE statements to Babelfish for Aurora PostgreSQL

To migrate a SQL Server database to Babelfish for Aurora PostgreSQL, you usually need to perform both automated and manual tasks. The automated tasks involve automatic code conversion using the Babelfish Compass tool with the -rewrite flag and data migration using AWS Database Migration Service (AWS DMS). The manual tasks involve database compatibility check using the Babelfish Compass tool, migration workarounds for certain database objects that are not supported by Babelfish, and manual validation of results.

In this post we focus on the Babelfish Compass tool’s -rewrite flag feature, which automatically converts the SQL Server’s MERGE statement to Babelfish-compatible T-SQL code. A MERGE statement is one example showed in this post, but -rewrite can also be used for other features. We also discuss best practices to manually validate the results.

Using Babelfish Compass, you can check whether your source SQL Server database is compatible with your target Babelfish database and what features aren’t currently supported in Babelfish and cannot be migrated.

PostgreSQL 15 Supports MERGE but Babelfish does not yet support it. Also, PostgreSQL MERGE is not fully equivalent to SQL Server’s MERGE , for example RETURNING and NOT MATCHED BY SOURCE clauses are not supported;

Babelfish Compass -rewrite flag overview

Utilizing the -rewrite flag, you can convert MERGE statements contained in the unsupported section of the Babelfish Compass assessment report.

Run the Babelfish Compass tool in the command prompt:

instructions for running compass on Mac and Linux.

BabelfishCompass.bat MyFirstReport C:\work\merge_example.sql -rewrite -reportoption xref

The -rewrite flag proves to be useful when the Babelfish Compass assessment report suggests to rewrite the SQL code manually so that it can be compatible with the Babelfish target.

You can remove some of the manual effort of converting SQL into Babelfish-compatible T-SQL code. However, you must also conduct a careful review of the corresponding rewritten SQL code.

Understanding the SQL Server MERGE statement

SQL Server’s MERGE combines INSERT, UPDATE and DELETE into a single statement and transaction. The MERGE statement selects rows from the source table and performs multiple DML operations on the target table in a single transaction.

To demonstrate scenarios of SQL Server MERGE, we create the following test tables in SQL Server database and in Babelfish.

Person_Target is the target table and Person_Source is the source, whose rows are merged into the Person_Target table based on the merge condition:

CREATE TABLE dbo.Person_Target(
PersonID int NULL,
PersonName varchar(100) NULL
)

CREATE TABLE dbo.Person_Source(
PersonID int NULL,
PersonName varchar(100) NULL
)

The following INSERT statements insert sample data into the Person_Source and Person_Target tables:

INSERT INTO Person_Source values 
(1,'Ana Carolina Silva') ,(3,'Carlos Salazar'), (4,'John Doe')
INSERT INTO Person_Target values 
(1,'Ana Carolina '),(2,'Diego Ramirez') ,(3,'Carlos Salazar')

The following code merges data from the Person_Source table into the Person_Target table, semicolon after MERGE is actually mandatory :

MERGE Person_Target T
USING Person_Source S ON T.PersonID=S.PersonID

WHEN MATCHED THEN
UPDATE SET PersonName=S.PersonName


WHEN NOT MATCHED BY TARGET
THEN
INSERT (PersonID,PersonName)
VALUES (S.PersonID,S.PersonName)

WHEN NOT MATCHED BY SOURCE
THEN
DELETE;

For each row in the Person_Target table, SQL Server evaluates the search condition known as the merge condition. If the condition is matched, the result becomes true and SQL Server updates the row in the target table with the corresponding data from the Person_Source table. If the condition isn’t matched for any rows, the result is false and SQL Server inserts the corresponding row from the source table into the target table. If the condition isn’t matched for any rows of source table then the row is deleted from target. The following diagram illustrates this workflow.

We can verify the target table and check if the data in the Person_Target table matches the preceding diagram.

Equivalent of SQL Server MERGE in Babelfish

For PostgreSQL doesn’t have a direct MERGE-like construct. However, the Compass tool can rewrite MERGE as individual INSERT, UPDATE or DELETE statements for Babelfish

The following statements create the Person_Source and Person_Target tables in Babelfish and insert the data:

CREATE TABLE dbo.Person_Target(
PersonID int NULL,
PersonName varchar(100) NULL
);
CREATE TABLE dbo.Person_Source(
PersonID int NULL,
PersonName varchar(100) NULL
);


INSERT INTO Person_Source values 
(1,'Ana Carolina Silva') ,(3,'Carlos Salazar'), (4,'John Doe');
INSERT INTO Person_Target values 
(1,'Ana Carolina '),(2,'Diego Ramirez') ,(3,'Carlos Salazar');

The following code is automatically generated inside a folder called rewritten located inside your report folder.

/* original MERGE statement -- MERGE Person_Target T
USING Person_Source S ON T.PersonID=S.PersonID

WHEN MATCHED THEN
UPDATE SET PersonName=S.PersonName


WHEN NOT MATCHED BY TARGET
THEN
INSERT (PersonID,PersonName)
VALUES (S.PersonID,S.PersonName)

WHEN NOT MATCHED BY SOURCE
THEN
DELETE; -- end original MERGE statement */

/*REWRITTEN*/ 
/* --- start rewritten MERGE statement #1 --- */
/* Note: please review/modify the rewritten SQL code below, especially for handling of ROLLBACK */
BEGIN TRANSACTION
SAVE TRANSACTION savept_merge_rewritten_1
DECLARE @MERGE_REWRITTEN_ROWCOUNT_1 INT = 0 /* use instead of original @@ROWCOUNT */
DECLARE @MERGE_REWRITTEN_ERROR_1 INT /* temporary variable */
DECLARE @MERGE_REWRITTEN_RCTMP_1 INT /* temporary variable */

/* WHEN MATCHED THEN UPDATE */
UPDATE T
SET PersonName=S.PersonName
FROM 
Person_Target T, 
Person_Source S
WHERE T.PersonID=S.PersonID
SELECT @MERGE_REWRITTEN_ERROR_1=@@ERROR, @MERGE_REWRITTEN_RCTMP_1=@@ROWCOUNT
IF @MERGE_REWRITTEN_ERROR_1 <> 0 GOTO lbl_rollback_merge_rewritten_1
SET @MERGE_REWRITTEN_ROWCOUNT_1 += @MERGE_REWRITTEN_RCTMP_1

/* WHEN NOT MATCHED BY SOURCE THEN DELETE */
DELETE T
FROM Person_Target T
WHERE NOT EXISTS (
SELECT * FROM Person_Source S
WHERE T.PersonID=S.PersonID
)
SELECT @MERGE_REWRITTEN_ERROR_1=@@ERROR, @MERGE_REWRITTEN_RCTMP_1=@@ROWCOUNT
IF @MERGE_REWRITTEN_ERROR_1 <> 0 GOTO lbl_rollback_merge_rewritten_1
SET @MERGE_REWRITTEN_ROWCOUNT_1 += @MERGE_REWRITTEN_RCTMP_1

/* WHEN NOT MATCHED BY TARGET THEN INSERT */
INSERT INTO Person_Target
(PersonID,PersonName)
SELECT S.PersonID,S.PersonName 
FROM Person_Source S
WHERE NOT EXISTS (
SELECT * FROM Person_Target T
WHERE T.PersonID=S.PersonID
)
SELECT @MERGE_REWRITTEN_ERROR_1=@@ERROR, @MERGE_REWRITTEN_RCTMP_1=@@ROWCOUNT
IF @MERGE_REWRITTEN_ERROR_1 <> 0 GOTO lbl_rollback_merge_rewritten_1
SET @MERGE_REWRITTEN_ROWCOUNT_1 += @MERGE_REWRITTEN_RCTMP_1

GOTO lbl_commit_merge_rewritten_1
/* in case of an error, roll back to savepoint at the start but do no abort the transaction: there may be an outermost transaction active*/
lbl_rollback_merge_rewritten_1: ROLLBACK TRANSACTION savept_merge_rewritten_1
lbl_commit_merge_rewritten_1:   COMMIT
;/* --- end rewritten MERGE statement #1 --- */

END
GO

One of the differences is that @@rowcount is different compared to SQL Server. That’s why we have @MERGE_RWRITTEN_ROWCOUNT_n in the rewritten code.

You can verify the data matches in the SQL Server and Babelfish PostgreSQL person_target table after the code is converted for the procedure.

Considerations

When the MERGE statement is dynamically constructed in a string variable, it won’t be rewritten using the Babelfish Compass tool. In such scenarios, you must convert it manually. The -rewrite flag only affects MERGE as long as Babelfish does not support it, once the feature is supported, -rewrite will not attempt to rewrite it anymore.

Conclusion

In this post we covered one example of MERGE statements used in SQL Server and explained how you can use the -rewrite flag in Babelfish Compass tool to convert them to equivalent Babelfish T-SQL code.

About the authors

Shyam Sunder Rakhecha is a Lead Consultant with the Professional Services team at AWS based out of Hyderabad, India and specializes in database migrations and modernization. He is helping customers in migration and optimization in AWS cloud. He is curious to explore emerging technology in terms of Databases. He is fascinated with RDBMS and Big Data. He also love to organize team building events and regalement in team.

AWS Database Blog