Obtain key of newly added record in loop

[glgcag]

I have created a stored proc that does the following:

1. Creates a one column recordset of client numbers from a union query (never more than 150 records)
2. Open a cursor and loop through the recordset inserting a new record into an expense statement table based on the current record.
3. Prior to going to the next record, I need to get the primary key from the newly created record to perform an update on the expenses table.

How do I get the StatementNumber(primary key of expense statements table) for the newly added record? Is there a Bookmark-type feature so I can reference it?

Here's the code from the stored proc:

--insert Union query into the cursor

OPEN c1
	FETCH NEXT FROM c1
    INTO @ClientKey

	-- loop until we run out of rows
	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @LastStatementNumber = Max(StatementNumber) 
		FROM tExpenses WHERE ClientKey = @ClientKey

		INSERT INTO tExpenseStatements (ClientKey, StatementDate, LastStatementNumber) 
		VALUES (@ClientKey, @RunDate, @LastStatementNumber)
	
	FETCH NEXT FROM c1
        INTO @ClientKey
	END
	CLOSE c1
	DEALLOCATE c1

 

[SQLSister]

use scope_Identity() Look up how to use it in BOL.

DO not use @@identity or you will eventually have data integrity problems.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

Since you can always be guaranteed that you will get a contiguous block of numbers, you don't need to use a loop at all.

Either by placing your to-be-inserted data into a temp table or table variable with an identity column and inserting that as a placeholder value in your data, or by utilizing key information from the inserted data, you can get all the information you need to do the insert in a single statement.

Insert all the records.
Get the scope_identity() value and the rowcount (careful to not have any statements between insert and rowcount) and you know the identity range of the inserted records: lastidentity - rowcount + 1 upto lastidentity.

Now joining back to these records and to your original insert table you can perform your second insert. One way I have done it is to insert generic, blank records where it doesn't matter which one is which. Then an update from the source data, then an insert to the child data via a join to the parent data on the same criteria as the update.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]