11

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.

20

Actually, you can achieve the same thing by changing your INSERT to a MERGE. While the MERGE 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:

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

MERGE INTO @MyTable AS dest
USING @MyInsertData AS ins ON 1=0   -- always false

WHEN NOT MATCHED BY TARGET          -- happens for every row, because 1 is never 0
    THEN INSERT ([Name])
         VALUES (ins.[NAME])

OUTPUT inserted.ID, ins.ID
INTO @MyCrossRef (NewId, OldId);

-- Check the result
SELECT * FROM @MyCrossRef

One of the nice things about MERGE is that it allows you to access the source columns as well as the built-in inserted and deleted tables in the OUTPUT 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.

  • That's great! This time round I'll have to stick to a loop because support for SQL Server 2005 is required. However I'll keep this in mind for future projects. Thanks! – Louis Somers Nov 21 '17 at 16:16
  • 3
    Brilliant, this should be the accepted answer. – Chris Peacock May 18 '18 at 17:47
  • 3
    Wish this was in stackoverflow instead of dba stackexchange. It has too little visibility. Amazing answer. – Lordbalmon Jun 4 at 21:56
  • 3
    Worked like a charm from first try... awesome answer! – BeemerGuy Jun 26 at 1:35
5

The output clause can only access data in the target rows and constants/variables, not data from elsewhere in the source SELECT, like if you were operating in a trigger.

https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql states:

Any reference to columns in the table being modified must be qualified with the INSERTED or DELETED prefix.

So to get the original ID you would need to include it in the destination table so that the output clause can echo it back, like so:

-- The existing table 
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX), SourceID INT);

-- 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], SourceID )
   OUTPUT Inserted.ID, Inserted.SourceID INTO @MyCrossRef
   SELECT [NAME], ID FROM @MyInsertData INS

-- Check the result
SELECT * FROM @MyCrossRef

though altering the schema of the target object may not be practical in your situation so this may not be applicable.

  • 3
    That's quite a disappointment. That renders the output clause useless for my scenario unless there is a 2nd column that can be used as a key :-( Oh, well, at least I can stop trying and get on with looping. Thanks. – Louis Somers Nov 17 '17 at 12:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.