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)  
         OUTPUT INSERTED.NoteId INTO #newNotes  
         SELECT Note.NoteText
             FROM Note  
             WHERE initial_report_number = @initialReportNum  
         INSERT INTO ClaimNote (claim_number, NoteId)  
   	     SELECT @claimNum, #newNotes.ID  
                 FROM #newNotes  
                 DROP TABLE #newNotes