SQL Server MERGE ステートメントを例にした SQL ステートメントの Babelfish for Aurora PostgreSQL 移行ベストプラクティス

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

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')

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;

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');

/* 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