Please start any new threads on our new site at https://forums.sqlteam.com. 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 

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

ja928
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
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
   

- Advertisement -