T-SQL: INSERT original value in OUTPUT clause

Question

I have a table Items (ItemID, Name, ...) where ItemID is auto-generated identity

I want to add rows into this table FROM select on this same table. AND save into table variable the references between OriginalItemID and NewlyGeneratedID.

So I want it to look like the following:

DECLARE @ID2ID TABLE (OldItemID INT, NewItemID INT);

INSERT INTO Items OUTPUT Items.ItemID, INSERTED.ItemID INTO @ID2ID
SELECT * FROM Items WHERE Name = 'Cat';

BUT Items.ItemID obviously does not work here. Is there a workaround to make OUTPUT take original ItemID from the SELECT statement?

You'd have to have something like a `OldItemID` on the target table which you can fill with your "old" ItemID value. Also: if that ItemID is auto-generated, you should **explicitly specify** the list of columns (everything except `ItemID`) both in your `INSERT INTO Items......` as well as the corresponding `SELECT (list of columns) FROM Items... ` statements. Otherwise, you'll be selecting "old" auto-generated ItemID values and trying to insert those into the auto-generated column again - which most likely won't even work — marc_s, May 20 '11 at 16:39
Good point. There is a mistake in my example. Anyway the problem remains. — Evgenyt, May 22 '11 at 15:16

Andriy M · Accepted Answer · 2019-05-29T19:10:41.037

If you are on SQL Server 2008+, you can use MERGE for getting both the current and the new ID. The technique is described in this question.

For your example the statement might look like this:

MERGE INTO
  Items AS t
USING
  (
    SELECT *
    FROM Items
    WHERE Name = 'Cat'
  ) AS s
ON
  0 = 1
WHEN NOT MATCHED BY TARGET THEN
  INSERT (target_column_list) VALUES (source_column_list)
OUTPUT
  S.ItemID, INSERTED.ItemID INTO @ID2ID (OldItemID, NewItemID)
;

T-SQL: INSERT original value in OUTPUT clause

1 Answers1

Linked