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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 08/13/2006 :  22:50:34  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
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 - 09/11/2010 :  08:05:09  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000