Merge statements emulation

In Synapse Analytics, the MERGE statement allows conditional INSERT, UPDATE, and DELETE operations to be performed in a single declarative block, simplifying synchronization and deduplication logic.

However, Microsoft Fabric Warehouse does not support MERGE, requiring developers to manually rewrite complex operations as separate queries — a process that can be time-consuming and error-prone.

SQL Tran automatically handles this incompatibility by splitting a MERGE block into individual UPDATE, INSERT, and DELETE statements that preserve the original intent. It rewrites each condition into a standalone operation, ensuring that the logical flow and data integrity are maintained.

This emulation eliminates the need for manual rewrites, simplifying the migration of complex ETL and data warehousing processes.

(For more information, see the following emulation reference page: MERGE statements)

---

Code example

Synapse Analytics:

MERGE INTO TargetTable AS t
USING SourceTable AS s
ON t.ID = s.ID
WHEN MATCHED THEN
    UPDATE SET t.Name = s.Name
WHEN NOT MATCHED THEN
    INSERT (ID, Name)
    VALUES (s.ID, s.Name)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

Fabric Warehouse (generated by SQL Tran):

DELETE FROM t
FROM
	TargetTable AS t
WHERE
	NOT EXISTS (SELECT 1
	FROM
		SourceTable AS s
	WHERE
		t.ID = s.ID);
UPDATE t SET
	t.Name = s.Name
FROM
	TargetTable AS t
	INNER JOIN SourceTable AS s ON t.ID = s.ID;
INSERT
	INTO TargetTable (ID, Name)
	SELECT s.ID,
		s.Name
	FROM
		SourceTable AS s
	WHERE
		NOT EXISTS (SELECT 1
		FROM
			TargetTable AS t
		WHERE
			t.ID = s.ID);

---

Training video example

In the example shown, a procedure contains a MERGE statement that synchronizes data based on matching conditions between source and target tables.

Since Fabric does not support MERGE, SQL Tran automatically translates the logic by generating separate UPDATE, INSERT, and DELETE statements in the target SQL.

Each part of the original MERGE is accurately emulated through deterministic operations, preserving both the correctness and intent of the original logic.

This preserves the original intent and ensures the code behaves consistently in Fabric’s environment, even without native MERGE support.

MERGE statement emulation in translated SQL