CODEWITHSUNDEEP
sqlsql-serverinsertidentity
spender
spender

Reputation: 120450

Getting new IDs after insert

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?

Upvotes: 49

Views: 37629

Answers (5)

Dana
Dana

Reputation: 719

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

Upvotes: 2

Daniel
Daniel

Reputation: 8368

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/

Upvotes: 1

SQLMaster
SQLMaster

Reputation:

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

Upvotes: 0

Jalpesh Vadgama
Jalpesh Vadgama

Reputation: 14216

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

Upvotes: 0

Robin Day
Robin Day

Reputation: 102478

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

Upvotes: 115

Related Questions