Gerador de upsert

Upsert no Azure Synapse: MERGE (dedicated SQL pool)

Nomeie tabela, chave e colunas e receba a instrução que o Azure Synapse realmente aceita, com uma guarda para que uma linha antiga nunca sobrescreva uma nova. Abaixo: como a mecânica funciona, o que quebra e como a Datrise carrega o Azure Synapse de forma incremental.

As notas técnicas desta página estão em inglês.

Gere a instrução

Instrução · 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]);

Como o upsert funciona no 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.

Antes de executar

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.

Perguntas frequentes

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.

O mesmo gerador para outros destinos

Pule a escrita do merge

A Datrise entrega entidades de CRM e SaaS no Azure Synapse com exatamente esta mecânica, uma marca d'água sobre updated-at e colunas tipadas, então a instrução acima é a que roda por você. Entre na lista de espera para acesso antecipado.

Explorar o catálogo de integrações