🔀Merge

NEW

An SQL operation that conditionally inserts, updates, or deletes target rows by matching a source to a target.

DESCRIPTION

MERGE simplifies synchronization between source and target tables, often used for upserts and CDC. It performs conditional insert/update/delete in a single statement based on matching criteria. Supported by Oracle, SQL Server, DB2, Snowflake and others. It reduces staging complexity and improves maintainability. Careful handling of concurrency, unique keys, and error conditions is essential.

ALTERNATIVE DEFINITIONS

Microsoft Learn

Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

Explicitly states conditional DML on join results.

Source

Oracle

Use the MERGE statement to select rows from one or more sources for update or insertion into a table.

Highlights selection and conditional update/insert.

Source

IBM Db2

The MERGE statement updates rows in the target table based on differences found in the source table and inserts new rows that do not exist.

Describes synchronization behavior in Db2 specifically.

Source

RELATED TERMS

Change Data Capture Slowly Changing Dimension SQL Data Warehouse Primary Key Join INSERT UPDATE DELETE

COMPETING TERMS

Upsert

KNOWLEDGE AREAS

Data Engineering Data Storage & Operations Data Modeling Data Integration & Interoperability Data Warehousing & BI

TARGET AUDIENCE

Data Engineer Analytics Engineer Data Architect Data Modeler Software Developer

EXTERNAL SOURCES

• Wikipedia - Merge (SQL)
• Microsoft - T-SQL MERGE documentation
• Oracle - Oracle MERGE statement guide
• Snowflake - MERGE in Snowflake SQL

TOP QUESTIONS

When should I use Merge in data pipelines?

Use MERGE for upserts, CDC, and synchronizing dimension tables without separate insert/update steps.

Category: Example

What are common mistakes with Merge?

Incorrect join keys, missing uniqueness constraints, and unintended deletes can lead to data corruption.

Category: Mistakes

Is MERGE idempotent?

Not inherently. Design predicates and keys to avoid duplicate updates and ensure idempotence if required.

Category: Misunderstanding

How does MERGE compare to UPSERT?

MERGE generalizes UPSERT by also supporting conditional delete and complex matching logic.

Category: Alternatives

What performance considerations exist for MERGE?

Clustered indexes, partitioning, and statistics affect MERGE performance; analyze execution plans.

Category: Requirements

Is MERGE standardized in SQL?

Yes, introduced in SQL:2008; implementations vary across vendors.

Category: Definition

How does MERGE support Slowly Changing Dimensions?

Use conditions to update current rows, insert new versions, and optionally expire old records.

Category: Example

How to avoid race conditions with MERGE?

Apply proper isolation levels, unique constraints, and consider staging to pre-aggregate source changes.

Category: Best practices

Can MERGE delete unmatched rows?

Yes, when specified; exercise caution to avoid unintended data loss.

Category: Risks