Getting new IDs after a bulk insert in SQL Server 2008

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

Answer 2

create a table to set all the new IDs. then make a loop for all the insert. inside the loop make the insert you want with SCOPE_IDENTITY(). after the insert get the new ID and insert it into the new table you created for. in the end select * from [newTable].

Answer 3

And if you want the "control" in ADO.Net and get the ids assigned to childs and getting the ids back so that you can update your model: http://daniel.wertheim.se/2010/10/24/c-batch-identity-inserts/