# Merge into a table Join / merge upsert merge into a target, upsert, slowly changing dimension Page: ## Status Runs as a SQL step. arcform carries it as a `sql:` step today. ## The DuckDB step merge a batch into a target It reads the `orders` table the proofs share. Create it first: ```sql CREATE TABLE orders AS SELECT * FROM (VALUES (1, 'Alice Ng', 'north', DATE '2026-01-03', 120.00, 'widget', 'a|b', '{"channel":"web","items":[1,2]}'), (2, 'alice ng', 'north', DATE '2026-01-05', 80.50, 'gadget', 'b', '{"channel":"shop","items":[3]}'), (3, 'Bob Okafor', 'south', DATE '2026-01-05', 300.00, 'widget', 'a|c', '{"channel":"web","items":[4,5,6]}'), (4, 'Carla Díaz', 'east', DATE '2026-01-09', NULL, 'gizmo', NULL, '{"channel":"web","items":[]}'), (5, 'Bob Okafor', 'south', DATE '2026-01-12', 45.25, 'gadget', 'c', '{"channel":"phone","items":[7]}'), (6, 'Dev Patel', 'west', DATE '2026-01-12', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (7, 'Carla Diaz', 'east', DATE '2026-01-20', 99.99, 'gizmo', 'b|c', '{"channel":"shop","items":[10]}'), (8, 'Dev Patel', 'west', DATE '2026-02-02', 510.00, 'widget', 'a', '{"channel":"web","items":[8,9]}'), (9, 'Eve Larsen', NULL, DATE '2026-02-03', 15.00, 'gadget', NULL, '{"channel":"web","items":[11]}'), (10, 'Alice Ng', 'north', DATE '2026-02-10', 220.00, 'gizmo', 'a|b|c', '{"channel":"phone","items":[12,13]}') ) t(order_id, customer, region, order_date, amount, product, tags, meta); ``` Then run the statement: ```sql CREATE TABLE target AS SELECT order_id, amount FROM orders WHERE order_id <= 5; UPDATE target SET amount = 0 WHERE order_id = 5; MERGE INTO target t USING (SELECT order_id, amount FROM orders WHERE order_id BETWEEN 5 AND 7) s ON t.order_id = s.order_id WHEN MATCHED THEN UPDATE SET amount = s.amount WHEN NOT MATCHED THEN INSERT VALUES (s.order_id, s.amount); CREATE TEMP TABLE result AS SELECT * FROM target; SELECT * FROM result ORDER BY order_id; SELECT CASE WHEN ((SELECT count(*) = 7 AND max(amount) FILTER (WHERE order_id = 5) = 45.25 AND max(amount) FILTER (WHERE order_id = 7) = 99.99 FROM result)) THEN 'as expected' ELSE error('the merge did not update order 5 and insert orders 6 and 7') END AS verdict; ``` It needs nothing installed beyond DuckDB itself. ## Variants - [Capture changes from a source](https://meridian.online/operations/merge-into-a-table/capture-changes-from-a-source): Read the rows a source has inserted, updated or deleted since the last read, from its log or change feed. - [Keep the history of changes](https://meridian.online/operations/merge-into-a-table/keep-the-history-of-changes): Close the current version of a changed row and add a new one, so the target keeps every past version with its validity period. - Process only new rows: Read and process only the rows that are new since the last run and add them to the existing result. - [Insert only new keys](https://meridian.online/operations/merge-into-a-table/insert-only-new-keys): Insert a source row only when its key, or its content hash, is not already in the target, leaving existing rows untouched. - [Replace a slice of the table](https://meridian.online/operations/merge-into-a-table/replace-a-slice-of-the-table): Delete the target rows in the partitions, time range or keys the new data covers, then insert the new rows. - [Apply inserts, updates and deletes by flag](https://meridian.online/operations/merge-into-a-table/apply-inserts-updates-and-deletes-by-flag): Insert, update or delete each target row according to a flag set on the incoming row. - [Update matching rows only](https://meridian.online/operations/merge-into-a-table/update-matching-rows-only): Overwrite values in the target rows whose key matches the source, inserting nothing. - Update or insert by key: Update the target rows whose key is in the source and insert the source rows whose key is not. ## What other tools call it Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2). - Azure Data Factory: [`Alter row`](https://learn.microsoft.com/en-us/azure/data-factory/data-flow-alter-row) (read 2026-09-27) - Azure Data Factory: [`Change data capture resource`](https://learn.microsoft.com/en-us/azure/data-factory/concepts-change-data-capture-resource) (read 2026-09-27) - Bruin: [`datavault_hub`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27) - Bruin: [`datavault_link`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27) - Bruin: [`datavault_satellite`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27) - Bruin: [`delete+insert`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27) - Bruin: [`ingestr: CDC (cdc: true)`](https://getbruin.com/docs/bruin/assets/ingestr.html) (read 2026-09-27) - Bruin: [`merge`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27) - Bruin: [`scd2_by_column`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27) - Bruin: [`scd2_by_time`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27) - Bruin: [`time_interval`](https://getbruin.com/docs/bruin/assets/materialization.html) (read 2026-09-27) - dbt-utils: [`check`](https://docs.getdbt.com/docs/build/snapshots) (read 2026-09-27) - dbt-utils: [`delete+insert`](https://docs.getdbt.com/docs/build/incremental-strategy) (read 2026-09-27) - dbt-utils: [`incremental`](https://docs.getdbt.com/docs/build/materializations) (read 2026-09-27) - dbt-utils: [`insert_overwrite`](https://docs.getdbt.com/docs/build/incremental-strategy) (read 2026-09-27) - dbt-utils: [`merge`](https://docs.getdbt.com/docs/build/incremental-strategy) (read 2026-09-27) - dbt-utils: [`microbatch`](https://docs.getdbt.com/docs/build/incremental-strategy) (read 2026-09-27) - dbt-utils: [`snapshot`](https://docs.getdbt.com/docs/build/snapshots) (read 2026-09-27) - dbt-utils: [`timestamp`](https://docs.getdbt.com/docs/build/snapshots) (read 2026-09-27) - DuckDB: [`INSERT`](https://duckdb.org/docs/current/sql/statements/insert) (read 2026-09-27) - DuckDB: [`MERGE INTO`](https://duckdb.org/docs/current/sql/statements/merge_into) (read 2026-09-27) - Duckle: [`DuckLake CDC`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`Merge / Upsert`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`PostgreSQL CDC`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25) - Duckle: [`SCD Type 1`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`SCD Type 2`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/website/docs/components.html) (read 2026-09-25) - Duckle: [`SCD Type 3`](https://github.com/slothflowlabs/duckle/blob/f85f8960edfb6efbf4cb89d58a7b32e16df0e867/frontend/src/workflow-ui/palette-data.ts) (read 2026-09-25) - AWS Glue: [`Merge`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-visual-job-api.html) (read 2026-09-27) - AWS Glue: [`mergeDynamicFrame`](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-crawler-pyspark-extensions-dynamic-frame.html#aws-glue-api-crawler-pyspark-extensions-dynamic-frame-merge) (read 2026-09-27) - Apache Hop: [`Combination lookup/update`](https://hop.apache.org/manual/latest/pipeline/transforms/combinationlookup.html) (read 2026-09-27) - Apache Hop: [`Dimension lookup/update`](https://hop.apache.org/manual/latest/pipeline/transforms/dimensionlookup.html) (read 2026-09-27) - Apache Hop: [`Insert / Update`](https://hop.apache.org/manual/latest/pipeline/transforms/insertupdate.html) (read 2026-09-27) - Apache Hop: [`Spark lake table merge`](https://hop.apache.org/manual/latest/pipeline/transforms/spark-lake-table-merge.html) (read 2026-09-27) - Apache Hop: [`Synchronize after merge`](https://hop.apache.org/manual/latest/pipeline/transforms/synchronizeaftermerge.html) (read 2026-09-27) - Apache Hop: [`Update`](https://hop.apache.org/manual/latest/pipeline/transforms/update.html) (read 2026-09-27) - KNIME Analytics Platform: [`DB Merger`](https://api.hub.knime.com/nodes/*Ze8BQNXdlzd3tGsO?details=full) (read 2026-09-27) - KNIME Analytics Platform: [`Table Updater`](https://api.hub.knime.com/nodes/*6MrPSJf3gPseMOiy?details=full) (read 2026-09-27) - Pentaho Data Integration: [`Combination lookup/update`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Pentaho Data Integration: [`Dimension lookup/update`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Pentaho Data Integration: [`Insert / Update`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Pentaho Data Integration: [`Synchronize after merge`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Pentaho Data Integration: [`Update`](https://docs.pentaho.com/pdia-data-integration/9.3-data-integration/pdi-transformation-steps-reference-overview) (read 2026-09-27) - Polars: [`DataFrame.update`](https://docs.pola.rs/api/python/stable/reference/dataframe/modify_select.html) (read 2026-09-27) - RapidMiner Studio: [`Update Database`](https://docs.rapidminer.com/latest/studio/operators/data_access/database/update_database.html) (read 2026-09-27) - RapidMiner Studio: [`Update MongoDB`](https://docs.rapidminer.com/latest/studio/operators/data_access/nosql/mongodb/mongodb_document_updater.html) (read 2026-09-27) - SQLMesh: [`INCREMENTAL_BY_PARTITION`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27) - SQLMesh: [`INCREMENTAL_BY_TIME_RANGE`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27) - SQLMesh: [`INCREMENTAL_BY_UNIQUE_KEY`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27) - SQLMesh: [`SCD_TYPE_2_BY_COLUMN`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27) - SQLMesh: [`SCD_TYPE_2_BY_TIME`](https://github.com/SQLMesh/sqlmesh/blob/23b237cea7218cc8f338b89c14f0957394a0003b/docs/concepts/models/model_kinds.md) (read 2026-09-27) - Tableau Prep: [`Incremental refresh`](https://help.tableau.com/current/prep/en-us/prep_incremental_refresh.htm) (read 2026-09-27) - Talend: [`Database change data capture components (family)`](https://help.qlik.com/talend/en-US/components/8.0/About) (read 2026-09-27) - Talend: [`Database slowly changing dimension ELT components (family)`](https://help.qlik.com/talend/en-US/components/8.0/About) (read 2026-09-27) - Talend: [`Database slowly changing dimension components (family)`](https://help.qlik.com/talend/en-US/components/8.0/About) (read 2026-09-27) - Talend: [`tIcebergMerge`](https://help.qlik.com/talend/en-US/components/8.0/iceberg/iceberg-component) (read 2026-09-27) - Talend: [`tSQLTemplateMerge`](https://help.qlik.com/talend/en-US/components/8.0/sqltemplate/sqltemplate-component) (read 2026-09-27) - Talend: [`tSqoopMerge (deprecated)`](https://help.qlik.com/talend/en-US/components/8.0/sqoop/sqoop-component) (read 2026-09-27) - VisiData: [`join-cols-merge`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L423) (read 2026-09-23) - VisiData: [`join-selected-merge`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L420) (read 2026-09-23) - VisiData: [`join-sheets-all-merge`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L422) (read 2026-09-23) - VisiData: [`join-sheets-top2-merge`](https://github.com/saulpw/visidata/blob/7609212ec2febafa9caba7183cbebfaf49627909/visidata/features/join.py#L421) (read 2026-09-23)