I am writing a batch processing insert statement and would like to use a temp table to keep track of inserted ID's instead of looping through the items myself and calling SCOPE_IDENTITY() for each inserted row.
The data that needs to be inserted has (temporary) ID's linking it to other data that also should be inserted into another table, so I need a cross reference of the actual Id and the temporary Id.
This is an example of what I have so far:
-- The existing table DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX)); -- My data I want to insert DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX)); INSERT INTO @MyInsertData ( ID,Name) VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last'); DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT); INSERT INTO @MyTable ( [Name] ) OUTPUT Inserted.ID, INS.ID INTO @MyCrossRef SELECT [NAME] FROM @MyInsertData INS -- Check the result SELECT * FROM @MyCrossRef
The problem is that I cannot get the OUTPUT INTO clause to accept the ID, I've tried
@MyInsertData.ID and other tricks joining the table to itself, but nothing seems to work.