Cannot insert explicit value for identity column in table ‘tblTestTable’ when IDENTITY_INSERT is set to OFF.

by Vijay Modi

Suppose you have a table in MS Sql with two Columns.

CREATE TABLE [dbo].[tblTest](
[Test_Id] [int] IDENTITY(1,1) NOT NULL,
[Test_Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[Test_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

Now you are inserting the three values in this table:

Insert Into tblTest(Test_Name) values(“Test1″)
Insert Into tblTest(Test_Name) values(“Test2″)
Insert Into tblTest(Test_Name) values(“Test3″)

So your table will look like as follows:

1 Test1
2 Test2
3 Test3

Now you are deleting second row of the table using the following syntax:

DELETE From tblTest where Test_Id=2

Now you table will looks like the following:

1 Test1
3 Test3

Now you are going to insert a row with the following query:

Insert Into tblTest(Test_Id,Test_Name) values(2, “TestTemp2″)

when you execute this query, you will face the following error:

This error is coming when we have a Identity Specification is ‘Yes’ and IsIdentity is also ‘Yes’, Identity Increament is set to(1/2/…).

So you are unable to insert this type of row, because sql know that the Test_Id is the Identity_Column and so you cannot insert the value which already inserted. So to resolve this error, you have to set the Column_Identity ON by using the following syntax.

SET IDENTITY_INSERT tblOrderItemStatus ON

Then try to insert the row using the above same query, which was as following:

Insert Into tblTest(Test_Id,Test_Name) values(2, “TestTemp2″)

Now you need to reset the Column_Identity OFF. You can do it just by the following syntax:

SET IDENTITY_INSERT tblOrderItemStatus OFF

Is it resolve your problem. Howdy buddy?….