Getting new IDs after insert

Question

I'm inserting a bunch of new rows into a table which is defined as follows:

CREATE TABLE [sometable](     [id] [int] IDENTITY(1,1) NOT NULL,     [someval] sometype NOT NULL ) 

using the following insert:

insert into sometable select somefield as someval from othertable 

when I've finished, I'd like to know the IDs of all the newly inserted rows. SCOPE_IDENTITY() only returns the ID last row inserted.

How can I get all the new IDs?

One method that springs to mind would be to grab the current largest identity from sometable and the scope_identity() post-insert, and use these two values to select from sometable. For example:

declare @currentMaxId int; select @currentMaxId=MAX(id) from sometable insert into sometable select somefield as someval from othertable select * from sometable where id>@currentMaxId and id<=SCOPE_IDENTITY() 

Is there a better pattern?

Answer 1

Use the OUTPUT functionality to grab all the INSERTED Id back into a table.

CREATE TABLE MyTable (     MyPK INT IDENTITY(1,1) NOT NULL,     MyColumn NVARCHAR(1000) )  DECLARE @myNewPKTable TABLE (myNewPK INT)  INSERT INTO      MyTable (     MyColumn ) OUTPUT INSERTED.MyPK INTO @myNewPKTable SELECT     sysobjects.name FROM     sysobjects  SELECT * FROM @myNewPKTable