Insert and select in one statement

Question

Possible Duplicate:
How to get last inserted id?

I have a table Absences

|  Id  |  Name  |  Job  |
-------------------------
|  1   |  James |   1   |
-------------------------
|  2   |  Simon |   1   |
-------------------------

Where ID is an identity Primary Key incrementing by 1.
I'm accessing this table from a program in C# and I need to do the following :

Insert Into Absences (Name, Job) Values ('aName', 'aJob')

The problem is I need to get the Id column where i'm inserting at the same time because Nameand Job are not unique so I won't be able to retreive this exact column after.

Is it possible to add a select on the Id column in that query ?

Update

  SqlConnection myConnection = new SqlConnection(@"SomeConnection");
  myConnection.Open();
  SqlCommand myCommand = myConnection.CreateCommand();
  myCommand.CommandText = "Insert Into Absences (Name, Job) Values ('aName', 'aJob')";
  int currentAbs = (int)myCommand.ExecuteScalar();

I get an error on the ExecuteScalar Line. Object reference is not set to and instance of object.

[Scope identity sometimes returns incorrect value](https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value). Use an `OUTPUT` clause as suggested in workarounds. — HABO, Aug 01 '12 at 12:53
@PhilMurray - AFAIK, it was fixed during the lifetime of 2008, but I haven't found a statement as to precisely when. It is marked as "Closed as fixed." — HABO, Aug 01 '12 at 13:07
I changed the type cast to `Convert.ToInt32(myCommand.ExecuteScalar());` and it worked... any idea why ? — phadaphunk, Aug 01 '12 at 13:08

Dave New · Accepted Answer · 2012-08-01T12:50:30.637

2

The SQL statement SCOPE_IDENTITY() will give you the value of the identity column of the newly inserted row from within the same scope.

SqlConnection myConnection = new SqlConnection(@"SomeConnection");
myConnection.Open();
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = "Insert Into Absences (Name, Job) Values ('aName', 'aJob'); SELECT SCOPE_IDENTITY();";
int currentAbs = (int)myCommand.ExecuteScalar();

Scope Identity Definition

edited Aug 01 '12 at 12:50

answered Aug 01 '12 at 12:36

Dave New

38,496
59
215
394

So this gives me the Highest current Identity ? – phadaphunk Aug 01 '12 at 12:37
1

No, because the highest identity is not necessarily the inserted row. – Dave New Aug 01 '12 at 12:39
Gives me another exception on the same line. Specified cast is not valid.. – phadaphunk Aug 01 '12 at 12:56
Can you check that the insert succeeded? – Phil Murray Aug 01 '12 at 12:58
@PhilMurray yes the insert works – phadaphunk Aug 01 '12 at 12:59
And if you run that SQL query straight on your database? What does it return? – Dave New Aug 01 '12 at 13:03
It returns the correct identity column... – phadaphunk Aug 01 '12 at 13:06
Ooooh I changed the type cast to `Convert.ToInt32(myCommand.ExecuteScalar());` and it worked... any idea why ? – phadaphunk Aug 01 '12 at 13:08
Weird. What was the exception being thrown? – Dave New Aug 01 '12 at 13:12

score 1 · Answer 2 · answered Aug 01 '12 at 12:35

1

If you use SqlCommand, then you can use

int lastId = (int)command.ExecuteScalar();

to retrieve the unique id of the inserted record.
Take a look at Microsoft page.

answered Aug 01 '12 at 12:35

Marco

56,740
14
129
152

Niice this sound pretty much like what I need although how can I access this from my C# program ? – phadaphunk Aug 01 '12 at 12:38
Mmm i get Object reference is not set to an instance of object.. I'll post my code above If you can take a quick look maybe i'm not using it right – phadaphunk Aug 01 '12 at 12:44
@PhaDaPhunk: which line gives you this error? – Marco Aug 01 '12 at 12:48
The executeScalar(). I added and update to my question with the code – phadaphunk Aug 01 '12 at 12:49

score 0 · Answer 3 · answered Aug 01 '12 at 12:38

0

After this query you can select @@identity to get the last inserted id in mssql server.

answered Aug 01 '12 at 12:38

PepeDeLew

346
4
15

2

Be careful with Identity and scoping. SCOPE_IDENTITY() is safer – Phil Murray Aug 01 '12 at 12:41

score 0 · Answer 4 · answered Aug 01 '12 at 12:39

One way would be to use SELECT @@IDENTITY immediately after you insert your record:

int id;
string query = "Insert Into Absences (Name, Job) Values ('aName', 'aJob')";
using (SqlCommand cmd = new SqlCommand(query, connection)) {
    connection.Open();
    // execute your INSERT query
    cmd.ExecuteNonQuery();
    // get the last-inserted ID
    cmd.CommandText = "SELECT @@IDENTITY";
    id = (int)cmd.ExecuteScalar();
}

Insert and select in one statement

4 Answers4