7

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

Ehsan
  • 1,662
  • 6
  • 28
  • 49

2 Answers2

19

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), ....
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
16

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), ...
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448