BeanSoftware Logo

ASP.NET Database Search Control

    Write For Us

How To Get Last Inserted ID On SQL Server

Many people use identity column as primary key in table. Identity column can increase value automatically whenever new row is added. After adding a new row, it is very common problem to find an ID of last inserted record. Depending of your case, there are some common solutions too.

I inserted a new record and want to know its ID

To get an ID of last inserted record, you can use this T-SQL:

INSERT INTO Persons (FirstName) VALUES ('Joe');
SELECT ID AS LastID FROM Persons WHERE ID = @@Identity;

You can use query like this inside stored procedure or as an ad-hoc query. After new row is inserted, second query will return only one number. Because only one number is returned, you can use this query with ExecuteScalar method of SqlCommand object.

I just need last table ID, but don't want to insert new row

If you use just second query from last example, @@Identity value will be NULL. To get last ID in this case, you can use MAX function with code like this:


Related articles:

1. SQL Queries For Paging In ASP.NET
2. Ad Hoc Queries in ASP.NET

FAQ toolbar: Submit FAQ  |  Tell A Friend  |  Add to favorites  |  Feedback

Copyright © 2002-2008 Bean Software. All rights reserved.