Home > Functions and DMVs, Insert, SQL Queries, SQL Server, T-SQL > Get the latest identity value inserted into a table / Identity value inserted in the current scope .

Get the latest identity value inserted into a table / Identity value inserted in the current scope .

September 15, 2009 Leave a comment Go to comments

It is often a requirement to fetch the latest/ current identity value used in a specific table . There are two types of identity values that you can fetch.

  • Depending on the scope of execution and irrespective of the table.
  • The value used in a table and irrespective of scope.

Let me show this to you, with an example. Create a table using the query

CREATE TABLE TestTable (
 TestID INT NOT NULL IDENTITY(1,1) ,
 TestValue NVARCHAR(50) NOT NULL )

Depending on the scope of execution and irrespective of the table

Insert a record into the table

INSERT TestTable( TestValue)
VALUES ( 'record 22')

SELECT SCOPE_IDENTITY() as RecentInserted

The function “SCOPE_IDENTITY()” prints the identity value used in the recent insert. This is irrespective of the table used. If you continue inserting record in any other table with Identity columns, you will notice the value returned by the function changing. This function does not accept any parameters.

The identity value used in a table

Use the function “IDENT_CURRENT() ” to get the current identity value used for a table . A sample query is as given below.

SELECT IDENT_CURRENT('TestTable') IdentityInTable

Notice that the table name has to be passed as a parameter for this function.

Also Check how to  Insert values into IDENTITY column of SQL server table

Advertisements
  1. No comments yet.
  1. February 10, 2015 at 6:57 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: