Get identifier after INSERT in SQL Server in multithreaded enviromment?

Question

How can I get the identifier after INSERT in SQL Server?

For my insertion I use context.ExecuteStoreCommand()

A warning. I have a multi-threaded application that inserts "at the same time" some data into the same table.

Answer 1

 SELECT SCOPE_IDENTITY() 

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

Answer 2

For this scenario, use Scope_Identity() . This will return Identity for the current scope. @@Identity returns the last inserted identifier.

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

Answer 3

try it

 @@identity 

below sample code

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

Answer 4

An alternative implementation method is to use OUTPUT in T-SQL.

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 the point of view of a general solution to the problem, I would advocate that you try to wrap your insertion logic in a stored procedure (returning the inserted record identifier) ​​that you call from the application source code.

Answer 5

I think you can write a stored procedure that has an in / out parameter and then grab the value from the parameter.

Answer 6

I am sure you will want to use the ObjectContext.ExecuteStoreQuery method if you need the identifier value and not ObjectContext.ExecuteStoreCommand .

You will need to use it because others mentioned SCOPE_IDENTITY() , not @@IDENTITY , because SCOPE_IDENTITY() returns the identifier value for the current execution area of ​​the application @@IDENTITY . "This is a system function, an 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 many situations where approximately 100 processes were written in one table at a time. I did not use SCOPE_IDENTITY() , but wrapped all the insertion / identification in the transaction, and there were no problems with this approach.

Answer 8

refer SQL Server - return value after INSERT

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