Generador de upsert

Upsert en Azure Synapse: MERGE (dedicated SQL pool)

Nombra tu tabla, clave y columnas y obtén la sentencia que Azure Synapse acepta de verdad, con una guarda para que una fila antigua nunca sobrescriba una nueva. Debajo: cómo funciona la mecánica, qué se rompe y cómo Datrise carga Azure Synapse de forma incremental.

Las notas técnicas de esta página están en inglés.

Genera la sentencia

Sentencia · 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]);

Cómo funciona el upsert en 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 ejecutarla

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.

Preguntas frecuentes

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.

El mismo generador para otros destinos

Sáltate escribir el merge

Datrise aterriza entidades de CRM y SaaS en Azure Synapse con esta misma mecánica, una marca de agua sobre updated-at y columnas tipadas, así que la sentencia de arriba es la que corre por ti. Únete a la lista de espera para acceso anticipado.

Explorar el catálogo de integraciones