# Insert only new keys

Join / merge upsert

A variant of [Merge into a table](https://meridian.online/operations/merge-into-a-table).

Insert a source row only when its key, or its content hash, is not already in the target, leaving existing rows untouched.

Page: <https://meridian.online/operations/merge-into-a-table/insert-only-new-keys>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

insert a source row only when its key, or its content hash, is new

```sql
CREATE TEMP TABLE source AS SELECT * FROM (VALUES
  (1, 'a', 1), (1, 'a2', 1), (3, 'c', 3), (3, 'c', 3), (4, 'b', 2), (NULL, 'n', 0), (5, NULL, 5)) t(id, name, qty);
-- by key: existing keys are left alone (key 1 keeps its values); the source keeps one row for each key, and a NULL key is not a key
CREATE TEMP TABLE by_key AS SELECT * FROM (VALUES (1, 'a', 1), (2, 'b', 2)) t(id, name, qty);
MERGE INTO by_key t USING (SELECT id, name, qty FROM source WHERE id IS NOT NULL QUALIFY row_number() OVER (PARTITION BY id ORDER BY name) = 1) s ON t.id = s.id
  WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.name, s.qty);
-- by content hash: a row is new when no target row has the same hash of its values; the key plays no part. The hash is SHA-256 of the row as JSON,
-- which does not change between DuckDB versions, so it can be stored; a NULL is 'null' in the JSON and an empty string is '""', so they differ
CREATE TEMP TABLE by_hash AS SELECT name, qty, sha256(to_json({'name': name, 'qty': qty})) AS row_hash FROM (VALUES ('a', 1), ('b', 2)) t(name, qty);
MERGE INTO by_hash t
USING (SELECT name, qty, sha256(to_json({'name': name, 'qty': qty})) AS row_hash FROM source QUALIFY row_number() OVER (PARTITION BY row_hash ORDER BY id) = 1) s ON t.row_hash = s.row_hash
  WHEN NOT MATCHED THEN INSERT VALUES (s.name, s.qty, s.row_hash);
SELECT * FROM by_key ORDER BY id;
SELECT name, qty FROM by_hash ORDER BY name NULLS LAST, qty;
SELECT CASE WHEN ((SELECT list((id, name, qty) ORDER BY id) = [(1, 'a', 1), (2, 'b', 2), (3, 'c', 3), (4, 'b', 2), (5, NULL, 5)] FROM by_key)
  AND (SELECT list((name, qty) ORDER BY name NULLS LAST, qty) IS NOT DISTINCT FROM [('a', 1), ('a2', 1), ('b', 2), ('c', 3), ('n', 0), (NULL, 5)] FROM by_hash)) THEN 'as expected' ELSE error('by key the target should gain keys 3 (once), 4 and 5 and keep key 1 as it was; by content hash it should gain a2, c, n and the NULL-name row, and not the repeat of a, or the row with key 4 whose values b and 2 are already there') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- 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)
- Apache Hop: [`Combination lookup/update`](https://hop.apache.org/manual/latest/pipeline/transforms/combinationlookup.html) (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)