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.

The cursor method involves inserting the rows one at a time so you can access the @@identity value for use in inserting to the child table. This works as long as there are no triggers on the table that insert rows into other tables, which themselves contain identity columns . In that case the @@identity value will be useless. I know of a system that does exactly that as it logs inserts to a log table that has an identity column on it. Attempting to work-around this issue by selecting the max(identity-column) is only a reasonable idea if you are the sole process inserting to the table (and there is no likelihood of that fact ever changing.)

An attempt at a set-based approach is to do the insert with the TABLOCKX hint and then subtract @@rowcount-1 from @@identity to get the range of identities that were inserted. However, I'm leery of this approach since hints are not commands: there's no guarantee that some other process didn't insert a row during our insert, skewing our range. Also, it is liable to the same potential trigger issue noted above. And, of course, locking the entire table may not be desirable.

In SQL Server 2005 we have the perfect solution. It will work regardless of triggers, and it will eliminate the need to do the inserts one at a time. It is the new OUTPUT clause available on the Insert, Update and Delete statements.

The OUTPUT clause lets you output data from the INSERTED or DELETED virtual tables which were formerly only accessible from within a trigger. In our case, we simply need to preserve the identity column (in a real-life example you would preserve any other column data you needed).

DECLARE @parent TABLE(myID INT IDENTITY, myCol INT)
DECLARE @newID TABLE(new_ID INT)
-- Generate 100 rows of test data
;WITH mycte AS 
(SELECT 100 AS seedValue
 UNION ALL 
 SELECT seedValue + 1 FROM mycte WHERE seedValue + 1 < 200
)
INSERT INTO @parent(myCol)
OUTPUT INSERTED.myId INTO @newID
SELECT * from mycte
SELECT * FROM @newID

The important part of this very simple example is:

OUTPUT INSERTED.myId INTO @newID

In the end, the @newID table variable will contain 100 new ID's guaranteed to be from the rows we just inserted into the @parent table variable.