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.
Best Answer
Actually, you can achieve the same thing by changing your
INSERT
to aMERGE
. While theMERGE
statement is actually a pretty neat way to do "upserts" in SQL Server, there's nothing to stop you from using it just for the purpose of inserting:One of the nice things about
MERGE
is that it allows you to access the source columns as well as the built-ininserted
anddeleted
tables in theOUTPUT
clause.My code may contain errors, as I haven't actually tested it. My blog post from a few years ago goes into a little more detail, including on query performance.