Cannot Insert Explicit Value For Identity Column In Table

 266 Total Views,  2 views today

This recipe will demonstrate how to Fix
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ‘YourTableName’ when IDENTITY_INSERT is set to OFF.

First create Problem:
CREATE TABLE employee (
EmpID INT NOT NULL IDENTITY(1,1),
EmpName VARCHAR(50))
GO

INSERT INTO employee ( EmpID, EmpName )
VALUES (1, ‘Leonel Messi’)
GO

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table ’employee’ when IDENTITY_INSERT is set to OFF.

Error Explain: This is because you are passing some value in a column which is set as identity (auto-increment).

Solution: 1
Set IDENTITY_INSERT property to ON for the table employee.

Sql Syntax:
SET IDENTITY_INSERT employee ON
INSERT INTO employee ( EmpID, EmpName )
VALUES (1, ‘Leonel Messi’);
SET IDENTITY_INSERT employee OFF

–>
select * from employee
Output:
EmpID EmpName
1 Leonel Messi

Solution: 2
Not to include the identity column in the INSERT statement and let SQL Server assign the next identity value to the record.

Sql Syntax:
INSERT INTO employee (EmpName)
VALUES (‘cristiano ronaldo’);
GO
select * from employee
Output:
EmpID EmpName
1 Leonel Messi
2 cristiano ronaldo

Leave a Reply

Your email address will not be published. Required fields are marked *