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.