---
title: "7 games became 14: making a bulk insert idempotent with an origin key"
canonical: https://dxdev.com/blog/idempotent-bulk-insert-origin-key-double-games/
datePublished: 2026-05-13
---
After I recovered a customer's deleted basketball site from a backup, one team's games table had 14 rows. The backup had 7. Same gamedate, same opponent, same opponent league ID, exactly two of each, with different new game IDs. This is the postmortem I wrote while the wound was fresh, and the one COUNT query that turned "why did every game get inserted twice" from an archaeology project into a five-second answer.

## The setup

The production app stores each org's site as rows keyed by `username` across hundreds of per-sport tables: `<app-schema>.basketballgames`, `<app-schema>.basketballscores`, `<app-schema>.basketballschedule`, and so on. "Recover this site" is not a `RESTORE DATABASE`. It's an application-level copy: re-insert all of those rows from a dated backup database into live prod, row by row, table by table.

The hard part of that copy is foreign keys. Child rows like scores point at parent rows like games by integer `gameID`, and the games table has `IDENTITY` primary keys, so you don't know the new game IDs until after the inserts run. The recovery engine handles this by inserting with offset placeholder IDs (it adds a large constant offset to every FK on the way in, parking it in a range that can't collide with real IDs), then doing a remap pass that rewrites every child FK to the real new value once the parents are in.

That two-phase shape, copy first then remap IDs, is the whole machine. And it's exactly the shape that bites you if any step isn't idempotent, because the natural failure mode of "copy a pile of rows into prod" is running it more than once.

## The symptom

One customer's account. Backup had 7 games. Target had 14. Not garbage rows: perfect duplicates. Each backup game showed up twice with the same gamedate, the same opponent, the same opponent league ID, just two distinct new `gameID` values.

The one telltale that mattered: on those duplicated rows, the `oldGameID` and `oldOpponentID` columns came back NULL.

That NULL is the whole case cracked open, but I didn't trust it yet. So before guessing, I wrote down the hypotheses.

## Three hypotheses, ranked

There are exactly three ways a games row ends up in prod twice. I ranked them by likelihood and by how to tell them apart.

**1. Recovery ran twice on a build that didn't stamp `oldGameID`.** The duplicate-guard works by reading back rows it already inserted and skipping them. If the guard keys on `oldGameID` but an earlier build never stamped that column, the second run can't recognize the rows the first run left behind, so it inserts them all again. The NULL `oldGameID` on exactly the duplicated rows is consistent with this: those rows predate the guard.

**2. A read/write split.** The guard reads through one connection while the insert writes somewhere else. If the read path and the write path don't point at the same place, the guard never sees its own writes and happily re-inserts everything on the same run.

**3. Genuine duplicates in the backup.** Maybe the source DB already had 14 games for this team and the recovery faithfully copied 14. Boring, but it has to be on the list, because if it's true then nothing in the recovery code is broken and "fixing" the guard would be chasing a ghost.

Three plausible stories, three completely different fixes. Guessing wrong here means either shipping a guard that doesn't help, or rewriting connection plumbing that was never the problem, or scrubbing data that was correct. So the next move is not to argue about which is most likely. It's to find the one cheap query that splits them apart.

## The five-second disambiguator

Hypothesis 1 (ran twice) and hypothesis 3 (dirty backup) make opposite predictions about one number: how many games the backup holds for this team.

```sql
SELECT COUNT(*)
FROM <backup-db>.<app-schema>.basketballgames
WHERE username = 'CUSTOMER';
```

If that returns 7, the backup was clean and prod has twice as many rows as the source, so the recovery ran twice. If it returns 14, the backup itself was dirty and the recovery copied exactly what it was handed. One COUNT, and two of the three hypotheses resolve.

The backup returned 7. Ran twice. (I also had the before-and-after exports side by side: 7 rows in the source, 14 in the target, the same gamedate and opponent showing up in pairs, and the offset values still sitting on the FKs in the after dump, the opponent league ID showing the original value plus the offset constant.)

And that lines up with what the NULL `oldGameID` was already telling me: those rows came from a run that predated the origin-key guard, so on the re-run there was nothing for the guard to match against. The read/write split (hypothesis 2) stayed possible in theory, but it didn't need to be true to explain anything, and the backup count plus the NULL column had already produced a complete, consistent story. I stopped there instead of going spelunking through connection strings to rule out a cause I no longer needed.

The temptation in a data-corruption incident is to start reading code, tracing connections, diffing builds. All of that is real work and most of it would have been wasted, because a single COUNT against the backup collapsed the decision tree before any of it started.

## The fix: stamp a stable origin key, guard on it

The reason this was ever a hard question is that the inserted rows carried no durable link back to where they came from. Two rows with the same gamedate and opponent might be a duplicate or might be a real doubleheader. Without an origin key you cannot tell, so the guard can't either, so re-running is unsafe by construction.

So the fix makes every inserted row remember its source. On every games insert, the recovery now stamps:

- `oldGameID` = the original `gameID` from the backup
- `oldOpponentID` = the original opponent league ID from the backup

And before inserting any game, it checks for one that's already there from the same source:

```sql
-- pseudo-code: look up an existing row by (username, oldGameID)
SELECT gameID FROM <app-schema>.basketballgames
WHERE username = ? AND oldGameID = ?
```

If that finds a row, the recovery reuses the existing `gameID` and skips the insert. Run it once, you get 7 games. Run it again, the guard matches all 7 on `(username, oldGameID)`, inserts nothing, and reuses the IDs that are already there. Idempotency by construction, not by the operator remembering not to double-click.

The origin key does double duty. It's the guard predicate, and it's also the answer to "did this already run." With `oldGameID` populated, "are these duplicates" is no longer a judgment call about matching gamedates. It's `GROUP BY username, oldGameID HAVING COUNT(*) > 1`, which is a fact, not an inference.

## The honest caveat

The guard only prevents *new* duplicates. It does nothing about the duplicates an earlier guard-less run already left in prod, which is exactly the mess that started this. Those are a one-off data fix: dedupe by `(username, oldGameID)`, or by `(username, gamedate, opponent)` for the pre-guard rows that have a NULL `oldGameID` and can't be keyed the clean way, then repoint the child rows (scores, stats) at the surviving game ID before deleting the loser.

Two distinct problems, and it's worth keeping them separate. The guard prevents future duplicates while the dedupe cleans up the ones already there. Conflating them is how you either over-build the guard or under-clean the data.

## Related

- [The offset-ID trick: copying rows whose foreign keys you don't know yet](/blog/offset-id-trick-remap-foreign-keys-restore/): the offset placeholder pattern that handles foreign keys during the copy
- [From 3.5 million round-trips to hundreds: batching a remap with temp tables](/batch-remap-temp-tables-3-5-million-round-trips): the performance side of the same copy-then-remap machine
- [1900-01-01 Is Not a Date: The Zero-Datetime That Corrupts Every Migration](/blog/1900-01-01-is-not-a-date-zero-datetime-migration/): another SQL Server data-integrity trap in bulk copy operations
- [I Wrote a 1,477-Line Site-Recovery Engine in Classic ASP: Copy First, Remap IDs Second](/blog/copy-first-remap-ids-second-site-recovery-engine/): the full recovery engine this idempotency guard protects