How to Get the Identity of an Inserted Row in SQL Server
Last Updated :
25 Jan, 2024
In SQL Server, we can use an Identity column, which will automatically increment the ID value in a table. The Identity column is a unique, sequential, and non-null value for each row in a table. The identity value is auto-incremented using the IDENTITY command. There could be situations when this auto-incremented value of the Identity column may be required.
In this article, we will discuss the different options available to get the identity value of the inserted row.
Identity of an Inserted Row
The identity of an inserted row is the last identity column value in a table which was automatically generated by the sql server database system when a new row is added to a table that has an identity column with auto increment set with initial and increment values like the below example.
EmployeeID Int NOT NULL IDENTITY (1,1)
In the above example, the EmployeeID column is created as an identity column to auto increment by 1 with an initial value starting from 1. When a new row is created in the table the EmployeeID will be 1 and it will increment the value by 1 after each row insert subsequently.
Getting the Identity Value from the Inserted Row
When an identity column is incremented while a new row is inserted, the same value may be required in the SQL to update or insert in another column or for any other processing of data like in Store procedure to update the EmployeeID value from employees table into a Managers table like the example below:
INSERT into Employees Values ('Raja Sekharan','.NET, AZURE, JAVA')
INSERT INTO Managers Values (SCOPE_IDENTITY(), 'Raja Sekharan', 'NA')Here the EmployeeID identity column value is updated to Managers table EmployeeID column when a new row is inserted into the Employees Table.
To get the identity value in SQL Server, there are 3 different options or system functions as below:
- SCOPE_IDENTITY()
- @@IDENTITY
- IDENT_CURRENT('TableName')
Let us look into these options in details with examples.
1. SCOPE_IDENTITY()
This function in SQL Server return the last identity value generated for any table in the current scope and session
INSERT into Employees Values ('Joel Thomas','.NET, AZURE, JAVA')
Select SCOPE_IDENTITY() as LastEmployeeIdOutput:
Below is the Identity value using Scope_Identity() for the last employee inserted
Identity value using scope_identity()When we run a select statement in Employee table the data will look like the below
Select * from employees where employeeID > 15
2. @@IDENTITY
The @@IDENTITY also returns the last identity value from a table insert with identity column. This value returned is from the current session but from across all scopes. When there are other statements that insert rows with identity column in the same session, it returns the last identity value.
INSERT into Employees Values ('Raja Mohan','.NET, AZURE,JAVA')
INSERT INTO Managers Values (@@IDENTITY, 'Raja Mohan', 'NA')
Select @@IDENTITY as LastIdentityValueOutput:
Below is the Identity Value using @@Identity
Identity value using @@identity()When we query both employees and managers table, below is the data
employees and managers table data3. IDENT_CURRENT('TableName')
This function returns the last Identity value inserted for a specific table across any scope and session. This will be helpful to get the last identity value of a table when multiple inserts are done by different users or when multiple inserts to different tables are done in a session and need to access the identity value of a specific table as given in the example below. The @@IDENTITY or SCOPE_IDENTITY() will return only the identity value from the last inserted value from last insert query.
INSERT into Employees Values ('Mohana Kumar','.NET, AZURE')
INSERT INTO Managers Values (@@IDENTITY, 'Mohana Kumar', 'Techical Manager')
Select IDENT_CURRENT('Employees') as LastEmployeeIDOutput:
Below is the Identity value of employee table using the IDENT_CURRENT
Identity value using Ident_CurrentWhen we query both employees and managers table, below is the data:
employees and managers table dataConclusion
Getting the inserted value from an Identity column is useful in many situations and this can done using the system functions instead of going to the table and finding the last identity value. But there can incorrect identity values returned when there are multiple inserts in a table or across many tables. So use the right system function to get the identity value based on session and scope of the insert done. Also note that the identity value is returned only from a Identity Column set with auto increment option.
Similar Reads
How to Get the Identity of an Inserted Row in SQL
In databases, Sometimes we need the identity of an inserted row in SQL. The identity value of the newly inserted row is very useful for various purposes in the database. In SQL, the RETURNING INTO clause within an INSERT statement enables retrieval of the inserted row's identity. When working with S
4 min read
How to Get the Identity of Last Inserted Row in MySQL?
Retrieving the last inserted ID from a MySQL database is a common task, especially when working with tables that have an AUTO_INCREMENT primary key. This can be essential for inserting related records into other tables, logging operations, or simply confirming that an insert operation was successful
3 min read
How to Turn IDENTITY_INSERT On and Off Using SQL Server?
IDENTITY_INSERT in SQL Server is a valuable tool in SQL Server 2008, allowing us to control how identity values are assigned when inserting new records into a table. IDENTITY_INSERT ON is a Transact-SQL statement that allows us to explicitly specify the value we want to insert into the identity colu
6 min read
How to get the insert ID in SQL?
When working with SQL databases, obtaining the insert ID (the auto-incremented primary key value) after inserting a new record is crucial for managing data relationships and ensuring seamless data referencing. In SQL databases, obtaining the insert ID after adding a new record to a table is a common
4 min read
How to Insert If Not Exists in SQL SERVER?
Adding Data to a table in SQL Server is a key operation. Data can be inserted into tables using many different scenarios like plain data inserted into a table without checking anything or checking if data already exists in the target table and only if the data does not exist then the new data is ins
7 min read
How to Update If Row Exists Else Insert in SQL Server
Data update and data insert are two important functions to add and update data in SQL Server Tables. Using SQL queries we can check for specific data if it exists in a table. The update query with the WHERE Clause can be used to update data passed from the front end or any other data generated from
6 min read
How to Insert Row If Not Exists in SQL
Managing and manipulating data in SQL is essential, especially when it comes to avoiding duplicate entries in a database. Duplicate records can lead to data inconsistencies and errors that disrupt operations and analysis. The "INSERT IF NOT EXISTS" feature in SQL acts as a safeguard, ensuring that o
6 min read
How to Join to First Row in SQL Server
Joining the first row in SQL Server can be a common requirement in various scenarios, such as when we need to retrieve specific data associated with the first occurrence of a particular group or when you want to fetch additional details related to the first record in a result set. In this article, W
4 min read
How to fix Cannot Insert Explicit Value For Identity Column in Table in SQL Server
While working on the SQL Server databases, we need to perform various operations like insert, update, and delete data from the update which are famously known as OLTP operations. In the Insert operation, we developers sometimes face the error message saying - 'Cannot insert explicit value for identi
7 min read
How to Add an IDENTITY to an Existing Column in SQL Server
It enables you to store, organize, and manipulate data in a relational format, meaning data is organized into tables. It Stores and manages data for dynamic web applications, ensuring effective user experiences. In this article, we will learn about How to add an identity to an existing column in SQL
5 min read