CODEWITHSUNDEEP
c#sql-serverinsertidentity
Patrice Pezillier
Patrice Pezillier

Reputation: 4576

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

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.

Upvotes: 7

Views: 24863

Answers (8)

aads
aads

Reputation: 2495

refer SQL Server - Return value after INSERT

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

Upvotes: 0

Daniel Mošmondor
Daniel Mošmondor

Reputation: 19966

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.

Upvotes: 0

Rob
Rob

Reputation: 45779

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

Upvotes: 0

John Sansom
John Sansom

Reputation: 41879

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.

Upvotes: 2

codingbadger
codingbadger

Reputation: 44024

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

Upvotes: 3

Numenor
Numenor

Reputation: 1706

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.

Upvotes: 13

RameshVel
RameshVel

Reputation: 65877

Try this 

@@identity

below the sample code 

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

Upvotes: 2

rlee923
rlee923

Reputation: 768

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

Upvotes: 0

Related Questions