CODEWITHSUNDEEP
c#sqlsql-server
Eamonn O'Brien
Eamonn O'Brien

Reputation: 123

Getting the primary key for an inserted value

I have a simple table with 2 columns - name and the primary key, id. When I insert a value, I wish to be able to retrieve the value straight away in my code.

So I have : 

db.Execute("INSERT INTO table(name) VALUES (@0)",name);

The id column is not automatically populated and the row is stored.

So how can I db.Query(); for this value when the name is not unique? Is this possible?

An interesting problem, I think :)

Upvotes: 2

Views: 7335

Answers (3)

Christian Van de Luitgaarden
Christian Van de Luitgaarden

Reputation: 36

Use db.ExecuteScalar and add SELECT SCOPE_IDENTITY() at the end of your SQL statement to retrieve the primary key.

Upvotes: 1

EBarr
EBarr

Reputation: 12026

You'll want to use SCOPE_IDENTITY() baring some unusual circumstance. Something like:

INSERT INTO table(name) VALUES (@0); SELECT Scope_Identity();

Why not @@identity? because it can be wrong in many scenarios. MSDN docs describe it as:

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

Upvotes: 3

BrokenGlass
BrokenGlass

Reputation: 160862

You can add a part to your insert that returns the identity using @@IDENTITY:

INSERT INTO table(name) VALUES (@0); SELECT @@IDENTITY;

Update: As noted in comments since @@IDENTITY works globally you should actually use SCOPE_IDENTITY() instead to limit to the current scope:

INSERT INTO table(name) VALUES (@0); SELECT SCOPE_IDENTITY();

Then you can retrieve the primary key / identity by executing your insert with ExecuteScalar() and grabbing the result.

Upvotes: 7

Related Questions