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.
| 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 @InsertedRowsSELECT [Name], ProductNumber, ListPriceFROM ProductsToInsert AS IWHERE NOT EXISTS (SELECT 1 FROM Product WHERE ProductNumber = I.ProductNumber)ORDER BY i.TempRowIdUPDATE ProductsToInsertSET InsertedIdentityValue = T.ProductIDFROM ProductsToInsert IJOIN @InsertedRows T ON T.TempRowId= I.RowIdI 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