|
ja928
Starting Member
USA
5 Posts |
 Posted - 09/11/2010 : 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 |
 |
|
Article: Using the OUTPUT Clause to Capture Identity Values on Multi-Row Inserts
