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:
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 anUPDATE ... FROM ... INNER JOIN
statement.Rewriting
WHEN MATCHED THEN DELETE
as aDELETE ... FROM ... INNER JOIN
statement.Rewriting
WHEN NOT MATCHED BY TARGET THEN INSERT
as anINSERT ... SELECT ... WHERE NOT EXISTS(...)
statement.Rewriting
WHEN NOT MATCHED BY SOURCE THEN DELETE
as aDELETE ... WHERE NOT EXISTS(...)
statement.Generating multiple statements when
MERGE
contains more than oneWHEN
clause.Translating the
MERGE
join condition into correspondingJOIN
orNOT 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:
Fabric Warehouse (generated by SQL Tran):
Important notes
Clause-by-clause translation: SQL Tran emulates
MERGE
behavior by translating eachWHEN
clause into a standaloneUPDATE
,DELETE
, orINSERT
statement, preserving the original join condition.Conditional update rewritten as
DELETE
: SQL Tran rewritesWHEN NOT MATCHED BY SOURCE THEN UPDATE
as aDELETE
with aWHERE 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 usingTOP(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 distinctUPDATE
orDELETE
statement with its respectiveWHERE
condition.Supported
VALUES
, subqueries, and common table expressions inUSING
clause: SQL Tran supportsMERGE
statements where the source is defined usingVALUES
, 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.
Last updated