How can I SELECT and then INSERT using the same data in a stored procedure?

Question

I have a Stored Procedure that runs and needs to return data as a rowset. It also needs to use that same data to insert into another table, but I am concerned that effectively running 'two' concurrent SELECT statement (even within a transaction) will potentially return two different results.

How can I ensure the same data is Inserted and then output correctly?

I believe the answer lies in the OUTPUT function - I've tried reading documentation and examples, but it doesn't quite seem to make sense for my scenario so wanted to check first before doing something way outside of best practice.

Example Code:

SELECT TOP 1 e.*
  FROM dbo.[example_selected_table] e

INSERT INTO dbo.[example_insert_table]
SELECT TOP 1 e.*
  FROM dbo.[example_selected_table] e

Not sure why `OUTPUT` "doesn't quite seem to make sense". This looks like a textbook application -- `INSERT INTO example_insert_table OUTPUT inserted.* SELECT ..` Do note that a `TOP` without an `ORDER BY` is not a very good example; you'd never want to use that in production. — Jeroen Mostert, May 30 '23 at 10:44
Well of course it seems simple once you know, but I didn't, which is why I asked. Thanks - it does indeed have an `ORDER BY` but I didn't include it for ease of showing what I'm trying to achieve. So you use `OUTPUT` with the table name and then have all available fields? — RazorKillBen, May 30 '23 at 10:55
this will preserve atomicity https://stackoverflow.com/questions/64061101/microsoft-sql-server-best-way-to-update-if-exists-or-insert — jmvcollaborator, May 30 '23 at 10:57

score 1 · Accepted Answer · answered May 30 '23 at 10:54

Use an OUTPUT clause to insert and return the inserted rows in a single-statement autocommit transaction:

INSERT INTO dbo.[example_insert_table]
OUTPUT inserted.*
SELECT TOP 1 e.*
  FROM dbo.[example_selected_table] e;

In your actual code, specify explicit column lists and an ORDER BY clause for deterministic behavior with TOP. TOP without ORDER BY may return an arbitrary row.

INSERT INTO dbo.[example_insert_table](Col1, Col2)
OUTPUT inserted.Col1, inserted.Col2
SELECT TOP 1 e.Col1, e.Col2
  FROM dbo.[example_selected_table] e
  ORDER BY e.Col1;

Thanks Dan - I missed the `ORDER BY` when creating example code, but it does contain it. That makes total sense to me, thanks for the example code! — RazorKillBen, May 30 '23 at 11:00

How can I SELECT and then INSERT using the same data in a stored procedure?

1 Answers1