Unanswered: How to get the @@Identity for multiple Inserts?
Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.
Since the insert is multiple iam not able to use the @@Identity.
Can some one suggest me How can i over come this situtation.
Also Triggers cant be used as the the records are of huge numbers.
INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_Users
INSERT INTO UserDependent(UserID,OtherFields)
At least for now, the @@identity values are processed as a block, in other words if you have five different inserts going (on different spids) at the same time, each block of the five will have a contiguous range of @@identity values. This means that if you insert 30 rows, the @@identity value will be for the first row, and @@identity+29 will be the value for the last row in your insert.
Note that this will change at some point in time, probably in the release after Yukon.