Get Id after an INSERT in SQL Server in a multithread enviromment?

Question

How can I get id after an INSERT in SQL Server ?

For my insert, I use context.ExecuteStoreCommand()

Warning: I have a multi-threaded application which inserts "at the same time" some data in the same table.

score 12 · Accepted Answer · answered Oct 12 '10 at 07:10

12

SELECT SCOPE_IDENTITY()

Use this after your insert statement and it will return you the identity of inserted in your scope. You can assign this to variable or return it in an output parameter.

answered Oct 12 '10 at 07:10

Numenor

1,686
12
20

yes they are. check http://msdn.microsoft.com/en-us/library/ms190315.aspx for detailed documentation for SCOPE_IDENTITY – Numenor Oct 12 '10 at 07:15
how does SCOPE_IDENTITY() differ from @@identity, SCOPE_IDENTITY() itself returns @@identity... and in my answer, i have returned the @@identity after the insert... – RameshVel Oct 12 '10 at 07:18
SELECT SCOPE_IDENTITY() return null after my INSERT ?! context.ExecuteStoreQuery("SELECT SCOPE_IDENTITY()") – Patrice Pezillier Oct 12 '10 at 07:28
1

you should call both your insert statement and Execute query in same context like: context.ExecuteStoreQuery("insert into table values(...); SELECT SCOPE_IDENTITY()") – Numenor Oct 12 '10 at 08:03

score 3 · Answer 2 · answered Oct 12 '10 at 07:13

You should use Scope_Identity() for this scenario. This will return the Identity for the current scope. @@Identity returns the last inserted identity.

Take a look at Scope Identity on MSDN - there are examples of how @@Identity will return incorrect values compared to using Scope_Identity

score 2 · Answer 3 · answered Oct 12 '10 at 07:08

2

Try this

@@identity

below the sample code

strSQL = "INSERT INTO tablename (name) VALUES (@name);SELECT @@Identity"
SQLCommand.CommandText = strSQL
Id = SQLCommand.ExecuteScalar()

answered Oct 12 '10 at 07:08

RameshVel

64,778
30
169
213

@Numenor - what are you talking about? That is perfectly thread safe, since both commands are being executed in a single query. – Damien_The_Unbeliever Oct 12 '10 at 07:13
@Patrice Pezillier, how do you say this is not thread safe.. did you check the insert statement at all?? – RameshVel Oct 12 '10 at 07:14
@Ramesh - When retrieving Identity values in MS SQL Server you should always use `Scope_Identity()` not `@@Identity`. Scope_Identity returns the identity value for the current scope where as @@Identity returns the last inserted identity. Which may or may not have been inserted in the current scope. See http://msdn.microsoft.com/en-us/library/ms190315.aspx for examples – codingbadger Oct 12 '10 at 07:21
7

(Didn't downvote) - SCOPE_IDENTITY is to be preferred over @@IDENTITY, mostly because of issues with triggers. But those claiming it's not thread safe are also mistaken, and are probably thinking of IDENT_CURRENT – Damien_The_Unbeliever Oct 12 '10 at 07:22
@Barry, thanks for the link, i understand that. But in this scenario the scope is single, so it wont cause any problems.... – RameshVel Oct 12 '10 at 07:24
@all: if some other thread using the same connection inserted some value to any table while your statements are being executed(you can not control this if your process is not the only process reaching the database), then @@Identity may return the other process's statements identity so it will cause a mistake. – Numenor Oct 12 '10 at 08:07
@Numenor - if some other thread is *somehow* executing statements on the *same* connection, then SCOPE_IDENTITY is equally likely to be screwed up. – Damien_The_Unbeliever Oct 12 '10 at 08:32
@Damien_The_Unbeliever: you are right about difference between @@IDENTTY and SCOPE_IDENTITY, i mistook it with IDENT_CURRENT. – Numenor Oct 12 '10 at 11:03

score 2 · Answer 4 · answered Oct 12 '10 at 08:35

An alternative implementation method is to use the OUTPUT clause of the T-SQL language.

For example:

create table #tmpTable 
(
    ID int identity(1,1) not null primary key,
    SomeValue varchar(20) not null
);

insert #tmpTable
output INSERTED.ID
values('SomeTextData')

drop table #tmpTable;

From an overall solution design perspective I would advocate that you look to wrap your insertion logic into a Stored Procedure (returning the inserted record ID) that you call from your application source code.

score 0 · Answer 5 · edited May 23 '17 at 12:32

0

refer SQL Server - Return value after INSERT

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

edited May 23 '17 at 12:32

Community

1
1

answered May 28 '13 at 06:00

aads

2,255
4
24
25

score 0 · Answer 6 · answered Oct 12 '10 at 07:08

0

I think you can write up a stored proc which has an in/out parameter, and then grab the value from the parameter.

answered Oct 12 '10 at 07:08

rlee923

758
3
14
28

score 0 · Answer 7 · answered Oct 12 '10 at 08:44

I'm fairly sure you'll want to use the ObjectContext.ExecuteStoreQuery method if you need the identity value, rather than ObjectContext.ExecuteStoreCommand.

You'll need to use, as others have mentioned SCOPE_IDENTITY(), rather than @@IDENTITY as SCOPE_IDENTITY() returns the identity value for the currente execution scope wheras @@IDENTITY "Is a system function that returns the last-inserted identity value."

Something like this should do the trick:

using(var context = GetAContextThatsReadyToUse())
{
    var valueForColumn1 = 5;
    var result = ExecuteStoreQuery<int>("INSERT INTO table1 (Column1) VALUES ({0});SELECT SCOPE_IDENTITY()", valueForColumn1);

    foreach(var item in result)
    {
        // Should only return one result, which is the identity value inserted by ExecuteStoreQuery
        Console.WriteLine(item);
    }
}

score 0 · Answer 8 · answered Oct 12 '10 at 08:48

0

I had lot of situations where something like 100 processes were writing at one table at a time. I didn't use SCOPE_IDENTITY() but were wrapping whole insert/ID fetch into an transaction, and there wasn't any problem with that approach.

answered Oct 12 '10 at 08:48

Daniel Mošmondor

19,718
12
58
99

Get Id after an INSERT in SQL Server in a multithread enviromment?

8 Answers8