SQL Server : Capturing multiple IDENTITY values after inserting into SQL Server table

Usually we will have situation where we need to capture IDENTITY values after inserting into table, normally this can be achieved by using SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT() functions.

IDENT_CURRENT: returns the last identity value generated for a specific table in any session and any scope.
@@IDENTITY: returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY : returns the last identity value generated for any table in the current session and the current scope.

The above functions are used to retrieve IDENTITY value for single record inserts.J

But, do you think these functions will satisfy all our requirements? My answer is NO, since they will not support for multiple inserts.

Nothing to worry, we have OUTPUT clause to capture the new IDENTITY values for a batch of inserted records which is explained below:

First, let us create sample table with few records.
CREATE TABLE [DBO].[Employee]
(
[EmployeeID] INT IDENTITY(1,1),
[EmployeeName] VARCHAR(100)
)
GO

INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Vamsi Priya');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Praveen');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Sushma');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Radhika');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Harini');
INSERT INTO [DBO].[Employee] ([EmployeeName]) VALUES ('Chaitanya');

The same insertion can be done in SQL Server 2008 as below :

INSERT INTO [DBO].[Employee] ([EmployeeName])
VALUES
('Vamsi Priya'),('Praveen'),('Sushma'),('Radhika'),('Harini'),('Chaitanya')
GO

After inserting the records, let us execute and check what is the last [EmployeeID] using the below query.

SELECT @@IDENTITY
GO

The output would be 6. Still we are not satisfied with the result as we got only last inserted employee id, but not all the IDs which were inserted in the transaction.

This can be achieved using the below query :

Before this let us remove all the records from [Employee] table.

TRUNCATE TABLE [DBO].[Employee]
GO

As we know, TRUNCATE TABLE removes all rows from a table, but the table structure and columns remains. If the table contains an identity column, the counter for that column is reset to the seed value defined for the column.

DECLARE @EMPTABLE TABLE ([EmployeeID] INT,[EmployeeName] VARCHAR(100));

INSERT INTO [DBO].[Employee]
(
[EmployeeName]
)
OUTPUT INSERTED.[EmployeeID],INSERTED.[EmployeeName]
INTO @EMPTABLE ([EmployeeID],[EmployeeName])
SELECT 'Vamsi Priya' UNION ALL
SELECT 'Praveen'     UNION ALL
SELECT 'Sushma'      UNION ALL
SELECT 'Radhika'     UNION ALL
SELECT 'Harini'      UNION ALL
SELECT 'Chaitanya'
GO

Now, let us see the output :

SELECT [EmployeeID],[EmployeeName] FROM @EMPTABLE
GO

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

Note :

1. I have used TABLE variable to capture the records, even TEMP TABLE also be used in place of TABLE variables.
2. You can use this statement inside your stored procedure and also the result can be used for any other calculations within SP.
3. This feature is available in SQL Server 2005 and above versions.