Retrieving Identities on set-based inserts
Posted by Viral Sarvaiya on January 20, 2010
A common situation where one is tempted to use cursors is this: You have a table with an identity column. You need to insert multiple rows to this table, then use the identities for further processing, such as inserting rows with those values into a child table.
for more details click Here.