Retrieve the latest value from an Auto-Incremented id-field?

Question

Suppose I have a Table:

FieldWorker{ ID, Name, WorkingArea}

And, ID is an auto-incremented field.

Now, suppose, I am using the following code to insert records:

sqlComm.ExecuteNonQuery("INSERT INTO [FieldWorker]
       ([Name]
       ,[WorkingArea])
 VALUES
       (@Name
       ,@WorkingArea)");

What technique can I use to retrieve the maximum value of auto-incremented ID field that has been generated upon the insertion of the latest record?

Both the insertion-code and the retrieval of ID-value must be done under a single SqlTransaction object.

Why must I use ExecuteScalar()?

possible duplicate of [How to get last inserted id?](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) — gbn, Feb 02 '12 at 09:40

score 0 · Answer 1 · answered Feb 02 '12 at 09:37

For SQL Server 2000, you'd need to do this with SCOPE_IDENTITY and ExecuteScalar

sqlComm.ExecuteScalar("INSERT INTO [FieldWorker]
       ([ID]
       ,[Name]
       ,[WorkingArea])
 VALUES
       (@ID
       ,@Name
       ,@WorkingArea);
       SELECT SCOPE_IDENTITY()");

For SQL Server 2005+, you use the OUTPUT clause

sqlComm.ExecuteScalar("INSERT INTO [FieldWorker]
       ([ID]
       ,[Name]
       ,[WorkingArea])
 OUTPUT INSERTED.ID
 VALUES
       (@ID
       ,@Name
       ,@WorkingArea);");

score 0 · Answer 2 · answered Feb 02 '12 at 09:39

0

1) You can set ID as Identity (auto increment) 2) You can get the last Identity number inserted as follow

@@IDENTITY

Reference SQL Identity Number

answered Feb 02 '12 at 09:39

Thit Lwin Oo

3,388
3
20
23

Not safe. Your link even states that. -1 for ignoring your own link – gbn Feb 02 '12 at 09:40

score 0 · Answer 3 · answered Feb 02 '12 at 10:05

If the ID field is autoincremented you don't need to include in your insert.

Further you can use Scope_Identity().

For more information you can visit this blog

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Retrieve the latest value from an Auto-Incremented id-field?

3 Answers3