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?

score 112 · Accepted Answer · answered May 01 '09 at 10:55

112

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

answered May 01 '09 at 10:55

Robin Day

100,552
23
116
167

5

@Robin - I've been working with SQL Server for years, but I learned two new things from your answer, OUTPUT, and the TABLE datatype. Thanks. – Corey Trager May 01 '09 at 11:39
1

I've also been using SQL for probably 15 years and had no idea OUTPUT existed, thanks! – Bob Radu Jun 22 '14 at 16:29
@Robin, this question may sound silly, but if I retrieve this primarykey value in the table 'myNewPkTable' inside a loop (cursor in this case), this value is going to be inserted in a row inside that table, yes? – DanielV Mar 30 '17 at 08:56
1

This is *the* solution..... until the table contains a trigger, then you run into a bug that Microsoft refuses to fix (`OUTPUT` cannot be used without `INTO` in a table with a trigger, and gives wrong values when `INTO` is used). The explanation and a possible workaround (for the `UPDATE` case at least) are listed here: https://stackoverflow.com/q/13198476/2557263 – Alejandro Nov 03 '17 at 20:13

Daniel · Answer 2 · 2010-10-25T06:27:44.457

1

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/

edited Oct 25 '10 at 06:27

answered Oct 24 '10 at 18:16

Daniel

8,133
5
36
51

2

Link no longer works. Site still works but only via https and site history doesn't go beyond 11/06/2010. – Randy Burden Feb 08 '18 at 22:39
Google cached version https://webcache.googleusercontent.com/search?q=cache:iVqxMunQvOMJ:https://danielwertheim.wordpress.com/2010/10/24/c-batch-identity-inserts/+&cd=1&hl=en&ct=clnk&gl=us – dynamiclynk Mar 02 '19 at 00:50

Dana · Answer 3 · 2021-05-13T18:00:50.513

Expanding on @Robin Day's answer -- you can map your temp Id's to the new Id's using a MERGE trick.

DECLARE @Keys TABLE (TempId INT, RealId INT)

MERGE MyTable
    USING @NewStuff t
    ON 1 = 0
    WHEN NOT MATCHED THEN
        INSERT (col1, col2, col3)
        VALUES (t.col1, t.col2, t.col3)
        OUTPUT t.TempId, Inserted.RealId INTO @Keys

From @Ivan Starostin's clever answer here: List of inserted ID in SQL Server

score 0 · Answer 4 · answered May 01 '09 at 11:19

User this stored Procuedure

this will be a dynamic primary key..


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_BulkInsertCountry
(
@FilePath varchar(1000)
)
AS
BEGIN--PROCEDURE
--variable declaration
declare @SQL varchar(500)
declare @id int
declare @CountryName varchar(30)

--Create temporary table for Country
CREATE TABLE #tmpCountry
(
CountryName varchar(30),
)

---executing bulk insert on temporary table
SET @SQL='BULK INSERT #tmpCountry from ''' + @FilePath + ''' WITH (FIELDTERMINATOR ='','',ROWTERMINATOR=''\n'')'
EXEC(@sql)

DECLARE cursor_Country CURSOR READ_ONLY FOR
select [CountryName] from #tmpCountry

OPEN cursor_Country
FETCH NEXT FROM cursor_Country INTO @CountryName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @id=isnull(max(Countryid),0) from tblCountryMaster
SET @id=@id+1
INSERT INTO tblCountryMaster values(@Id,@CountryName) 
FETCH NEXT FROM cursor_Country INTO @CountryName
END
CLOSE cursor_Country
DEALLOCATE cursor_Country
END--PROCEDURE

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

For More details visit following link http://jalpesh.blogspot.com/search?q=bulk+insert

score 0 · Answer 5 · answered May 01 '09 at 17:03

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].

Getting new IDs after insert

5 Answers5

Linked