Design a SQL upsert from a staging table

What's really being asked

Whether you can design a daily load that is idempotent, meaning rerunning it produces the same result, and that correctly separates new records from updates without creating duplicates or losing rows on partial failure.

The full answer

First define the key that identifies a record uniquely, ideally a primary or natural business key, not a surrogate that changes each load. Deduplicate the staging table before merging, because the same key can arrive twice in one batch; keep only the latest version per key using a window function such as ROW_NUMBER partitioned by the key and ordered by a timestamp. Then perform the upsert. In SQL Server or Oracle use MERGE, matching staging to production on the key, with WHEN MATCHED THEN UPDATE and WHEN NOT MATCHED THEN INSERT. In Postgres use INSERT ... ON CONFLICT (key) DO UPDATE, and in MySQL use INSERT ... ON DUPLICATE KEY UPDATE. Wrap the operation in a transaction so a mid-batch failure rolls back cleanly.

The mistakes people make

A bare INSERT that duplicates existing rows, or a delete-then-insert pattern that leaves the table empty if the insert fails after the delete. Forgetting to dedupe staging, which causes MERGE to error or apply nondeterministic updates.

What usually comes next

How do you handle soft deletes from the source? What about concurrent loads hitting the same rows? How would you track which rows changed for downstream consumers?

A concrete example

A nightly job loads orders into staging. The analyst runs ROW_NUMBER over order_id ordered by updated_at descending and keeps rank one, then issues a single MERGE into the production orders table on order_id, updating status and amount for matches and inserting brand-new orders, inside one transaction so a crash leaves production untouched.