What does INSERT INTO events (id, kind) SELECT id + 1000, kind FROM events do to the table it reads?
answer
- ask what the query is allowed to see
- the statement's own rows are not re-read
- the source reflects the state at statement start
- doubling once, not looping
basics
~20 sIt duplicates the table exactly once. The source query is defined to see the table as it was when the statement started, so the rows being inserted are not re-read; three rows become six, not an endless loop.
solid answer
~50 sWhen the source of an `INSERT ... SELECT` is the target table itself, SQL defines the query to be evaluated against the table's state at the start of the statement. The newly inserted rows are therefore invisible to the query that produces them, so a table of three rows becomes six and the statement terminates. Without that rule the statement would be self-feeding and could never finish, which is why the language pins it down rather than leaving it to the engine. It is a genuinely useful idiom — doubling a fixture table for test data, back-filling a shifted key range, cloning one tenant's rows — but it is also a footgun: it is easy to write against a large table, it multiplies row count in one unit of work, and a unique constraint on the shifted column is often the only thing that will stop you inserting nonsense.
go deeper
Recognise that the source of an INSERT can be the very table being written, and be able to say the table simply doubles rather than growing without end.
Explain why: the source query is evaluated against the table's state at statement start, so the rows the statement inserts are never fed back into it, which makes the row count deterministic.
Show you have used it and respect it: cloning a tenant's rows or growing a fixture table is routine, but a missing WHERE fails silently, and doubling a big table is one all-or-nothing unit of work.
Decide when self-sourced copies belong in operational procedures at all — what safeguards (dry-run SELECT, key ranges, unique constraints, size limits) make the pattern acceptable against production data.
## The statement ```sql -- events currently holds 3 rows: ids 1, 2, 3 INSERT INTO events (id, kind) SELECT id + 1000, kind FROM events; -- afterwards: 6 rows — 1, 2, 3, 1001, 1002, 1003 ``` The target of the INSERT and the source of the SELECT are the same table. The obvious worry is that inserting row 1001 gives the query a new row to read, which produces 2001, which produces 3001, forever. ## The rule that prevents it SQL defines a data-change statement to operate on the state of the database as of the start of the statement. The source query of an `INSERT ... SELECT` is evaluated against that state, so rows the statement itself inserts are not visible to it. The statement reads exactly the three rows that existed when it began, inserts exactly three rows, and stops. This is a *language* guarantee, not an implementation detail you should reason about from one engine's observed behaviour: it is what makes the idiom safe to write. How an engine achieves it — materialising the source, or some other mechanism — is the engine's business, and the general class of hazard it is avoiding has a name in the query-processing literature, but you need none of that to know what the statement returns. ## Why anyone writes this - **Growing a fixture table.** Repeated doubling is the fastest way to turn a hand-written 100-row test table into 100,000 rows: run the statement several times, with a key offset each time. - **Back-filling a shifted range.** Copying a set of rows into a reserved key range, or under a new tenant or period identifier, without a round trip to the client. - **Cloning rows inside the database.** Any transformation the select list can express — mapping a status, renaming a kind, stamping a literal — is applied on the way through. ```sql -- clone one tenant's config rows for a new tenant INSERT INTO settings (tenant_id, name, value) SELECT 42, name, value FROM settings WHERE tenant_id = 7; ``` That is the same pattern with a filter and a literal, and it is a common production task. ## Where it goes wrong **The key collides.** If the target has a primary key or unique constraint and the select list does not produce fresh values for it, every row collides and the statement fails as a whole. That is the constraint doing its job; a table without such a constraint accepts the duplicates in silence, which is worse. **The filter is missing.** `INSERT INTO settings (tenant_id, name, value) SELECT 42, name, value FROM settings` without the `WHERE` copies every tenant's rows under tenant 42. Because the statement reads the pre-statement state, it still terminates — it just terminates having done something large and wrong, with no error to warn you. **The table is big.** Doubling a table is a single statement whose size is proportional to the table, and it does that work as one unit. On a large table this is not something to fire off casually against production; the language-level point is that one statement means one all-or-nothing outcome — if it fails at the end, none of it happened. ## Reading a different table you also write The same start-of-statement rule is what makes `INSERT ... SELECT` predictable in general: you never have to reason about a query racing the rows its own statement produces. What *other* concurrent transactions can see, and when, is a separate matter governed by isolation rather than by the shape of this statement. ## A safety habit Run the source query on its own first, as a plain `SELECT`, and look at both the rows and the count. Because a self-sourced insert always terminates cleanly, the SELECT is the only place where a wrong predicate or a missing filter is cheap to notice. ## Answering it well Say the outcome first — the table doubles, exactly once, and the statement terminates — then give the reason: the source is evaluated against the state at statement start, so the statement's own inserts are invisible to it. Adding a real use (doubling fixtures, cloning one tenant's rows with a literal in the select list) and the real risk (a missing WHERE, a colliding key, a large table in one unit of work) shows you have used the idiom rather than just parsed it.
- What stops the statement from feeding on the rows it inserts?The language: a data-change statement operates on the database state as of the start of the statement, so the source query sees only the rows that existed before it ran. Its own inserts are invisible to it, which makes the row count deterministic — n rows in, n rows inserted. How an engine implements that guarantee is its own affair.
- When would you actually reach for a self-sourced INSERT ... SELECT?To grow a fixture table by repeated doubling with a key offset, to clone one tenant's or one period's rows under a new identifier with a literal in the select list, or to back-fill a reserved key range. All of them keep the data inside the database instead of pulling rows to the client and pushing them back.
- What is the most common way this statement goes wrong in practice?A missing or wrong WHERE clause. Since the statement terminates cleanly either way, there is no error to warn you — it simply copies far more rows than intended, under the wrong key or tenant. Running the source query as a plain SELECT first, and checking its row count, is the cheap habit that catches it.
saying these in an interview costs you the question
- Claims the statement loops forever or grows without bound
- Thinks the behaviour here is undefined or engine-specific luck
- Says it inserts only one row because the source is the target
- Assumes a duplicate key silently overwrites the existing rows
- Believes the newly inserted rows are re-read by the same query