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.

Answer 1

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

Answer 2

Try this

@@identity

below the sample code

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

Answer 3

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.

Answer 4

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

Answer 5

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.

Answer 6

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);
    }
}

Answer 7

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.