How to Insert Multiple Values into Multiple Tables in a Single Statement? – Interview Question of the Week #290

Question: Can one INSERT populate two tables? Yes. INSERT writes the main table, and OUTPUT inserted... INTO can capture those inserted values in a second table.

This question comes up in my Comprehensive Database Performance Health Check work. I prefer explicit data flow to unnecessary trigger side effects. OUTPUT is often a simple answer when the second operation is capturing what the first operation inserted.

That preference doesn’t mean a trigger’s every business rule can be replaced by this pattern. Triggers and declarative constraints have their own roles. Here, the goal is specifically to copy the inserted values into a second destination.

One insert, two populated destinations

The original example used ordinary Table1 and Table2 with global cleanup. This version keeps the same columns and values in private table variables, so you won’t accidentally create or drop an application’s existing tables:

DECLARE @Table1 table (ID1 int, Col1 varchar(100));
DECLARE @Table2 table (ID2 int, Col2 varchar(100));
INSERT INTO @Table1 (ID1, Col1)
OUTPUT inserted.ID1, inserted.Col1 INTO @Table2 (ID2, Col2)
VALUES (1,'Col'), (2,'Col2');
SELECT ID1, Col1 FROM @Table1 ORDER BY ID1;
SELECT ID2, Col2 FROM @Table2 ORDER BY ID2;

Both destinations contain (1, Col) and (2, Col2). The INTO column list makes the mapping explicit: ID1 goes to ID2, and Col1 goes to Col2. OUTPUT doesn’t infer that mapping from similar column names.

Where the pattern fits

It can capture generated identity values or other inserted columns without selecting the table again. It isn’t an unrestricted multi-table INSERT that can arbitrarily modify every destination. The OUTPUT INTO target has restrictions, including no enabled triggers, no foreign-key participation, and no CHECK constraints or enabled rules.

The inserted values exposed by OUTPUT reflect the DML operation before AFTER triggers run. Don’t assume they include later changes made by a trigger. Row order is not guaranteed; the SELECT statements use ORDER BY only to make the displayed results predictable.

Keep error handling and transaction semantics explicit in a larger workflow. A client-side OUTPUT result by itself is not proof that a transaction committed successfully.

My earlier posts cover OUTPUT with INSERT, UPDATE and DELETE and simple OUTPUT examples. Microsoft: OUTPUT documents the destination and trigger restrictions.

This is an interesting interview question because it tests whether someone understands the data flow, not just the syntax. Share a useful variation through LinkedIn.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.