Saturday, January 15, 2011

Retrieving Identities on Bulk Inserts

If you need to insert a bunch of records at one time, and need to get the identity of all the inserted records, you can use a cursor.  But sometimes, using a cursor just feels wrong.  The “Inserted” key word comes to the rescue!  Use it like the following.  In SQL2008 they have further improved this so that you don’t even need the temp table.

     CREATE TABLE #newNotes (ID INT)  
INSERT INTO Note (NoteText)
SELECT Note.NoteText
WHERE initial_report_number = @initialReportNum
INSERT INTO ClaimNote (claim_number, NoteId)
SELECT @claimNum, #newNotes.ID
FROM #newNotes
DROP TABLE #newNotes

No comments:

Post a Comment