SCOPE_IDENTITY for multiple records

sql, sql-server, sql-server-2008, sql-server-2012

Solution

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.

Problem

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 ```

Original source