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
– marc_s, Commented May 20, 2011 at 16:39
Good point. There is a mistake in my example. Anyway the problem remains. — Evgenyt
– Evgenyt, Commented May 22, 2011 at 15:16

Andriy M · Accepted Answer · 2019-05-29 19:10:41Z

21

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)
;

edited May 29, 2019 at 19:10

answered May 20, 2011 at 18:19

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

T-SQL: INSERT original value in OUTPUT clause

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related