SQL @@IDENTITY

The SQL @@IDENTITY is a System Function that returns the last inserted identity value. You can use this Sql Server @@IDENTITY after an INSERT, INSERT INTO SELECT, BULK INSERT, or SELECT INTO Statement is completed to find the lastly generated identity value. And, if there is no insert operation happened then it will return NULL.

In this article we will show you, How to write use this @@IDENTITY in SQL Query to extract identity value with example. For this we are going to use the below shown table data.

We are creating following table to insert the data into it.

USE [SQL Tutorial]
GO

CREATE TABLE [dbo].[EmployeeDuplicates](
	[EmpID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](255) NULL,
	[LastName] [nvarchar](255) NULL,
	[Education] [nvarchar](255) NULL,
	[Occupation] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL,
	[HireDate] [datetime] NULL,
 CONSTRAINT [PK_EmployeeDuplicates_EmpID] PRIMARY KEY CLUSTERED 
 (
	[EmpID] ASC
 )
)

SQL @@IDENTITY Example 1

In this example first, we will insert four random records into the Employee Duplicates table. Next, we will extract the last inserted identity value using the @@IDENTITY in Sql Server

Let me insert value into the table using the INSERT Statement.

-- Example for SQL Server IDENTITY 
USE [SQL Tutorial]
GO

INSERT INTO [dbo].[EmployeeDuplicates]
	  ([FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales], [HireDate])
     VALUES
           ('Tutorial', 'Gateway', 'Masters','Admin', 92500, 3200, '2006-01-28 13:10:02.047'),
	   ('Steve', 'Lara', 'Graduate', 'Software Developer', 19500, 1200, '2012-04-28 13:10:02.047'),
	   ('Jack', 'Smith', 'Graduate', 'Manager', 25000, 150.45, '2015-05-26 13:12:02.047' ),
	   ('Ramesh', 'Kumar', 'Post Graduate', 'Sales Manager', 75000, 240.89, '2016-04-18 13:10:02.047')
GO

SELECT @@IDENTITY AS [Last Identity Value]
GO

SELECT MAX(EmpID) AS [Maximum Identity Value]
FROM [EmployeeDuplicates]
GO

OUTPUT

Let me show you the data that we inserted into the Employee Duplicates tables

OUTPUT

@@IDENTITY Example 2

In this example first, we will use the INSERT INTO SELECT Statement to insert records from Employee table into the Employee Duplicates table. Next, we will extract the last inserted identity value in Sql Server using the @@IDENTITY

-- Example for SQL Server IDENTITY 
USE [SQL Tutorial]
GO
INSERT INTO [EmployeeDuplicates] (
[FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales], [HireDate])
   SELECT [FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales], [HireDate]
   FROM [Employee] AS Emp
   WHERE Emp.EmpID > 4
GO

SELECT @@IDENTITY AS [Last Identity Value]
GO

SELECT MAX(EmpID) AS [Maximum Identity Value]
FROM [EmployeeDuplicates]
GO

OUTPUT

Let me show you the data that we inserted into the Employee Duplicates tables

OUTPUT

SQL @@IDENTITY Example 3

In this example first, we will show you what will happen if the Insert statement fails for some reason. To demonstrate this @@IDENTITY, we are deliberately failing the insert operation.

-- Example for SQL Server IDENTITY 
USE [SQL Tutorial]
GO

INSERT INTO [dbo].[EmployeeDuplicates]
	  ([FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales], [HireDate])
     VALUES
           ('Tutorial Gateway', 'Website', 'Programming','Student', 2000,  '2006-01-28 13:10:02.047')
GO

SELECT @@IDENTITY AS [Last Identity Value]
GO

SELECT MAX(EmpID) AS [Maximum Identity Value]
FROM [EmployeeDuplicates]
GO

OUTPUT

Let me show you the data that we inserted into the Employee Duplicates tables

OUTPUT

As you can see, It is returning NULL value.