Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2006-08-13 : 22:50:34
SQL Server 2005 introducted the OUTPUT clause which we can use to capture values from the inserted and deleted virtual tables. Previously this data was only available through triggers. We can use this in an INSERT ... SELECT statement to capture all the inserted identity values. Previously this required some type of loop or temporarily altering the target table.

Article Link.

Starting Member

5 Posts

Posted - 2010-09-11 : 08:05:09
This is a really good article. I was searching for a way to get rid of a cursor when I had to insert rows in multiple parent-child tables. One underlying assumption in this is that of the Natural Key for joining @InsertedRows back to ProductsToInsert. In the absence of a Natural key, could you just add another Identity field to @InsertedRows then use an ORDER BY in your INSERT INTO Products to reliably JOIN the two? Something like:

DECLARE @InsertedRows TABLE (ProductID INT, TempRowId INT IDENTITY(1,1) )
INSERT Product ([Name], ProductNumber, ListPrice)
OUTPUT inserted.ProductID
INTO @InsertedRows
[Name], ProductNumber, ListPrice
ProductsToInsert AS I
WHERE ProductNumber = I.ProductNumber)
ORDER BY i.TempRowId

UPDATE ProductsToInsert
SET InsertedIdentityValue = T.ProductID
FROM ProductsToInsert I
JOIN @InsertedRows T ON T.TempRowId= I.RowId

I don't know for certain that the rows in the OUTPUT clause / inserted table may not be in the same order as the insert. Thanks in advance
Go to Top of Page

- Advertisement -