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