How to get last inserted Record ID in SQL Server
by GetCodeSnippet.com • June 17, 2013 • Microsoft SQL Server • 0 Comments
Auto increment identity column automatically insert record Id with the specified incremental value. Several times we need to get the last inserted record id. There are several ways to achieve this task. Let’s take a look how we can get last inserted record/row id value.
@@IDENTITY
Most common and important method used to get last inserted value is @@IDENTITY. It returns the last inserted value from any table in the current session. For example, if we are using any trigger insert record into another table as a result of insert statement of a table then @@IDENTITY will return the record id from latest inserted table. So it considers current session and it is limited to current session only.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE PROCEDURE dbo.GetInsetedRecordID ( @CompanyName NVARCHAR(50), @ContactName NVARCHAR(50), @City NVARCHAR(50) ) AS BEGIN INSERT INTO Customers (CustomerName, ContactName, City) VALUES (@CompanyName, @ContactName,@City) SELECT @@IDENTITY AS CustomerID END |
SCOPE_ IDENTITY ()
Another method we can use to get last inserted value is SCOPE_ IDENTITY(). It considers your current session but it also considers your current scope. That means it will return last inserted value in current scope and it will not return last inserted value generated from any trigger of user defined functions. In other words you can say that it will only return the last inserted value that you explicitly insert rather than the value generated from any trigger or user defined function.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE PROCEDURE dbo.GetInsetedRecordID ( @CompanyName NVARCHAR(50), @ContactName NVARCHAR(50), @City NVARCHAR(50) ) AS BEGIN INSERT INTO Customers (CustomerName, ContactName, City) VALUES (@CompanyName, @ContactName,@City) SELECT SCOPE_IDENTITY() AS CustomerID END |
IDENT_CURRENT(‘TableName’)
As you can guess, IDENT_CURRENT(‘TableName’) will return last inserted value of specified table. It will not consider any session or scope. It only returns the last inserted value of that specified table regardless of any session or scope.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE PROCEDURE dbo.GetInsetedRecordID ( @CompanyName NVARCHAR(50), @ContactName NVARCHAR(50), @City NVARCHAR(50) ) AS BEGIN INSERT INTO Customers (CustomerName, ContactName, City) VALUES (@CompanyName, @ContactName,@City) SELECT IDENT_CURRENT('Customers') END |
There are other ways you can use to get last inserted value from SQL Server database table but generally these techniques are not considered safe.
You can use following statements but be careful while using these statements to get last inserted value because sometimes it can cause problems.
|
1 2 |
SELECT MAX(CustomerID) FROM Customers |
|
1 2 3 4 |
SELECT TOP 1 FROM Customers ORDER BY CustomerID DESC |
