How do you archive rows into a history table and delete them without round-tripping through the application?

## The shape of the job "Move rows older than a year into history" is one of the most common batch tasks there is, and it has an obvious wrong implementation: `SELECT` the rows into the application, `INSERT` them back one at a time into the archive table, then `DELETE` them one at a time. Every row crosses the network twice and costs three statements. The set-based version keeps the data server-side entirely. ## Step 1: copy with INSERT ... SELECT ```sql INSERT INTO orders_archive (id, customer_id, total, closed_at) SELECT id, customer_id, total, closed_at FROM orders WHERE status = 'CLOSED' AND closed_at < DATE '2024-01-01'; ``` One statement, no rows in flight to the client, and the engine can stream directly from the source into the target. Always write explicit column lists on both sides: a positional `SELECT *` into an archive table silently breaks the day either table gains a column. ## Step 2: delete — and the trap The naive second statement repeats the predicate: ```sql DELETE FROM orders WHERE status = 'CLOSED' AND closed_at < DATE '2024-01-01'; ``` This is correct only if the set of rows matching that predicate cannot change between the two statements. It usually can. An order that is closed by ordinary application traffic in the seconds between the `INSERT` and the `DELETE` — with a `closed_at` value that satisfies the range, or under any predicate over mutable state — now matches the `DELETE` but was never copied. The row is destroyed. The failure is silent and permanent, and it will not reproduce in testing where nothing else is writing. ## The fix: pin the set, not the predicate Decide *once* which rows are moving, and let both statements refer to that decision: ```sql -- 1. Freeze the batch INSERT INTO orders_to_archive (id) SELECT id FROM orders WHERE status = 'CLOSED' AND closed_at < DATE '2024-01-01' ORDER BY id FETCH FIRST 5000 ROWS ONLY; -- 2. Copy exactly those rows INSERT INTO orders_archive (id, customer_id, total, closed_at) SELECT o.id, o.customer_id, o.total, o.closed_at FROM orders o JOIN orders_to_archive b ON b.id = o.id; -- 3. Delete exactly those rows DELETE FROM orders WHERE id IN (SELECT id FROM orders_to_archive); ``` Now the copy and the delete are provably over the same rows, because both are driven by the frozen key list. The same trick gives you chunking for free — the `FETCH FIRST` bounds each batch, and the job loops until the freeze step selects nothing. ## Atomicity Wrap steps 2 and 3 in one transaction. If the process dies between them, you otherwise get one of two bad states: rows copied but not deleted (a re-run duplicates them in the archive) or rows deleted but not copied (data loss). One transaction per chunk keeps each unit small and makes a crashed job simply *unfinished* rather than inconsistent. Belt and braces: give the archive table the same primary key as the source, so an accidental repeat copy raises a key violation instead of silently duplicating. ## One-statement variants Several engines can do the move atomically in a single statement, which removes the divergence problem by construction. PostgreSQL allows a data-modifying CTE: ```sql WITH moved AS ( DELETE FROM orders WHERE status = 'CLOSED' AND closed_at < DATE '2024-01-01' RETURNING id, customer_id, total, closed_at ) INSERT INTO orders_archive (id, customer_id, total, closed_at) SELECT id, customer_id, total, closed_at FROM moved; ``` SQL Server expresses the same idea with `DELETE ... OUTPUT deleted.* INTO orders_archive`. Both are vendor extensions rather than standard SQL, so portable code stays with the two-statement, pinned-set form — but if you are on an engine that has one, it is the cleanest answer available. ## Ordering around foreign keys If child tables reference the rows being moved, the archive copy has to happen for children too, and the deletes have to run child-first (or the constraint has to be defined with a cascading action). Getting this order wrong shows up immediately as a constraint violation rather than silently, which is the one mercy in this job. ## What to say in an interview Name `INSERT ... SELECT` as the mechanism that keeps rows server-side; then, unprompted, raise the same-predicate-twice hazard and the pinned-key-set fix; then transaction scoping per chunk; then, if the engine offers it, the single-statement `RETURNING`/`OUTPUT` variant. That progression — mechanism, correctness hazard, fix, engine shortcut — is exactly the reasoning the question is testing.