Get Identity of multiple insertion in sql server 2

Question

This question already has an answer here:

• How to insert multiple records and get the identity value? 7 answers

Hi
Im going to insert 10 rows in a time using one TSQL which is available in SQL server 2008. I want the IDENTITY of inserted rows. I think the below solution would work but Im not sure if some other insertion happens while im running the previous insertion would affect the result

INSERT INTO tableA VALUES (1,2), (3,4), (4,5), ....
DECLARE @LastID INT = @@IDENTITY
SELECT TOP(10) ID FROM tableA WHERE ID<=@LastID ORDER BY ID DESC

Answer 1

You can use the OUTPUT clause:

DECLARE @tablevar table (ID int);

INSERT INTO tableA 
OUTPUT INSERTED.ID INTO @tablevar 
VALUES (1,2), (3,4), (4,5), ....

Answer 2

Just use the OUTPUT clause - it can either return output to the application, or into a table variable for further work.

E.g. your query would be:

INSERT INTO tableA
OUTPUT inserted.ID
VALUES (1,2), (3,4), (4,5), ...