Upsert generator

Upsert into Azure Synapse: MERGE (dedicated SQL pool)

Name your table, key and columns and get the statement Azure Synapse actually accepts, with a guard so an older row never overwrites a newer one. Below it: how the mechanic works, what breaks, and how Datrise loads Azure Synapse incrementally.

Generate the statement

Statement · MERGE (dedicated SQL pool)

MERGE [deals] AS t
USING [deals_staging] AS s
  ON t.[id] = s.[id]
WHEN MATCHED AND s.[updated_at] > t.[updated_at] THEN UPDATE SET
  t.[name] = s.[name],
  t.[stage] = s.[stage],
  t.[amount] = s.[amount],
  t.[owner_id] = s.[owner_id],
  t.[updated_at] = s.[updated_at]
WHEN NOT MATCHED BY TARGET THEN
  INSERT ([id], [name], [stage], [amount], [owner_id], [updated_at])
  VALUES (s.[id], s.[name], s.[stage], s.[amount], s.[owner_id], s.[updated_at]);

How the upsert works in Azure Synapse

Azure Synapse dedicated SQL pools support T-SQL MERGE, with restrictions that come from the distributed engine: the target must be a hash-distributed or round-robin table, targets with IDENTITY columns are not supported, and table hints such as HOLDLOCK are not accepted, which is why the generated statement omits them. Serverless SQL pools have no MERGE at all; they read the lake and write with CETAS.

Performance is about data movement. Distribute the staging table on the same column as the target so every distribution merges its own slice and the plan shows no ShuffleMove. For very large batches, CTAS a new table from a UNION of unchanged and changed rows and rename it into place; that avoids the row-by-row update path entirely.

Before you run it

In Synapse dedicated SQL pools MERGE runs on hash-distributed and round-robin targets; replicated tables and targets with IDENTITY columns are not supported, and table hints like HOLDLOCK are not accepted.
Distribute the staging table on the same key as the target so the join is local to each distribution and avoids a data-movement (ShuffleMove) step.
Serverless SQL pools are read-only over the lake and have no MERGE; land with CETAS there, or MERGE in a dedicated pool.

Questions people ask

MERGE fails on my replicated dimension table. Why?

Replicated tables cannot be a MERGE target in dedicated pools. Recreate the dimension as hash-distributed or round-robin, or use UPDATE plus INSERT.

Can I upsert from a serverless SQL pool?

No. Serverless pools are read-only over the lake. Either MERGE in a dedicated pool, or write a new Parquet partition and dedupe at read time.

How do I avoid the ShuffleMove step?

Give staging and target the same hash distribution column and join on it. Check the plan with EXPLAIN before scheduling the batch.

The same generator for other destinations

Skip writing the merge at all

Datrise lands CRM and SaaS entities into Azure Synapse with this exact mechanic, a watermark on updated-at, and typed columns, so the statement above is what runs on your behalf. Join the waitlist to get early access.

Browse the integration catalog