SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
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.

ja928
Starting Member

USA
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
SELECT
[Name], ProductNumber, ListPrice
FROM
ProductsToInsert AS I
WHERE
NOT EXISTS (SELECT 1 FROM Product
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  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000