CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Billa
Billa

Reputation: 5266

SCOPE_IDENTITY for multiple records

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

Upvotes: 5

Views: 3329

Answers (1)

marc_s
marc_s

Reputation: 754488

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.

Upvotes: 10

Related Questions