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

May 30, 2023

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

>Solution :

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;