SCOPE_IDENTITY for multiple records

Question

The below query inserts many records in a transaction. I want to fetch the newly created incremental identifier and use it in next INSERT statement

For a single record I can use like below

 SELECT @new_emp_id= SCOPE_IDENTITY()     

What about SCOPE_IDENTITY for multiple records? Or we can insert it into temp table and loop through it?

 INSERT EmployeeBenifits(EmployeeId,BenifitID,StartdateTime,EndDateTime) 

 SELECT @new_emp_id,BenifitID,GetDate(),@PassedEndDate FROM Benifits

 INSERT EmployeeBenifitDetails(EmpBenId,Desc)
 SELECT EmpBenId,'Created Details' FROM @NewlyInsertedEmplBenifits

Answer 1

You should have a look at the OUTPUT clause:

INSERT INTO dbo.EmployeeBenefits(EmployeeId, BenefitID, StartdateTime, EndDateTime) 
   OUTPUT Inserted.EmployeeBenefitId, Inserted.EmployeeID, Inserted.BenefitID   -- or whatever you want to return here
   SELECT 
      @new_emp_id, BenefitID, GetDate(), @PassedEndDate 
  FROM 
      dbo.Benefits

Basically, the OUTPUT clause can return a result set of columns for each row that has been inserted (also works with the DELETE and UPDATE statements).

See MSDN documentation for more details

You can also send the OUTPUT rows to a temporary table (or table variable) to be further processed later on.