How to get IDENTITY values on Multi-Rows insertion in SQL Server

In general problem scenario, we need to capture the IDENTITY value after insertion operation in a table. This is achieved by using SCOPE_IDENTITY()@@IDENTITY and IDENT_CURRENT() function.

The above functions doesn’t works in case of multi-rows insertion.

SQL Server 2005 introduced the OUTPUT clause as part of INSERT statement which we can use to capture IDENTITY  values on multi-rows insertion.

Problem Scenario : We have a Employee table and insert multiple rows at a time and get the generated IDENTITY values.

Let’s Create Sample Employee Table.

1
2
3
4
5
6
CREATE TABLE EMPLOYEE
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	Name NVARCHAR(200),
	Gender NVARCHAR(10)
)

Now, Find the T-SQL Code script using OUTPUT Clause to get the generated IDENTITY values.

1
2
3
4
5
6
7
DECLARE @outTable TABLE (ID INT);
 
INSERT INTO EMPLOYEE (Name, Gender) 
	INSERTED.ID INTO @outTable(ID)
	VALUES ('Jackson Smith', 'Male'),('James Nirva','Gender')
 
SELECT ID FROM @outTable

From the above example, you could understand how we can capture the multiple identity values. 

If you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning  🙂

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.