MERGE statements

Emulation context

In Microsoft SQL Server, the MERGE statement is used to perform conditional INSERT, UPDATE, or DELETE operations in a single, set-based command. It is commonly used for:

• Slowly changing dimensions in data warehousing

• Conditional synchronization of staging and target tables

• Deduplicating data during ETL workflows

The MERGE command combines all update logic into a single, declarative block that simplifies transformation logic across datasets.

For example:

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;

Microsoft Fabric Warehouse does not support the MERGE statement. Instead, merge-like logic must be expressed through explicit UPDATE, INSERT, and DELETE statements.

---

Emulation strategy

SQL Tran emulates MERGE logic by splitting the single MERGE block into individual UPDATE, INSERT, and DELETE statements that preserve the intent of the original logic. Each WHEN clause is translated into a standalone operation. The emulation process includes:

• Rewriting WHEN MATCHED THEN UPDATE as an UPDATE ... FROM ... INNER JOIN statement.

• Rewriting WHEN MATCHED THEN DELETE as a DELETE ... FROM ... INNER JOIN statement.

• Rewriting WHEN NOT MATCHED BY TARGET THEN INSERT as an INSERT ... SELECT ... WHERE NOT EXISTS(...) statement.

• Rewriting WHEN NOT MATCHED BY SOURCE THEN DELETE as a DELETE ... WHERE NOT EXISTS(...) statement.

• Generating multiple statements when MERGE contains more than one WHEN clause.

• Translating the MERGE join condition into corresponding JOIN or NOT EXISTS subqueries.

• Retaining aliases and logic when the USING clause references a common table expression (CTE) or a subquery.

• Omitting unsupported features such as TOP(n) and locking hints (e.g., WITH (HOLDLOCK)) without diagnostic comments.

Each translation reflects the logical behavior of the original MERGE, structured as multiple deterministic operations to preserve correctness.

---

Code example

SQL Server:

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

MERGE statements emulation in SQL Tran

---

Important notes

• Clause-by-clause translation: SQL Tran emulates MERGE behavior by translating each WHEN clause into a standalone UPDATE, DELETE, or INSERT statement, preserving the original join condition.

• Conditional update rewritten as DELETE: SQL Tran rewrites WHEN NOT MATCHED BY SOURCE THEN UPDATE as a DELETE with a WHERE NOT EXISTS(...) condition. This is necessary because Fabric does not support updating rows that are unmatched by source.

• TOP(n) is silently removed: MERGE statements using TOP(n) are translated without preserving the row limit. The resulting statements apply to all qualifying rows. No diagnostic is emitted.

• Locking hints are discarded: Hints such as WITH (HOLDLOCK) are not supported in Fabric and are removed by SQL Tran without warning.

• Multiple WHEN MATCHED branches handled separately: SQL Tran translates each conditionally matched branch (e.g., WHEN MATCHED AND ...) into a distinct UPDATE or DELETE statement with its respective WHERE condition.

• Supported VALUES, subqueries, and common table expressions in USING clause: SQL Tran supports MERGE statements where the source is defined using VALUES, a subquery, or a common table expression (CTE). Join logic and structure are preserved.

• Manual validation recommended: Although SQL Tran emulates merge logic accurately, users should review the output for performance, sequencing, and correctness — especially when original logic involves complex joins, conditional logic, or deduplication.