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

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?….

About these ads

About Vijay Modi

Having 10+ years of experience in web application development. Expertise in various domains like E-Commerce, E-Learning, Insurance. I have expertise in web application development, window application development, Performance improvement, bug fixing etc. I am believing in quality work and achieving deadlines. Also like to work on new technologies and quick learner.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

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

  1. Stasigr says:

    Hello, very nice site, keep up good job!
    Admin good, very good.

  2. JASnipes says:

    Thanks! The Explanation helped me a lot! never would have found that error. Keep up the good work!

  3. alok goenka says:

    thanx

    helped me a lot solving the above problem

    again tahx

  4. Himal says:

    Hi, I also had this problem, found your advice useful, now the problem is gone! Thank you.

  5. Farhan Ahmed says:

    Gr8 work Pal…. its really nice…
    It solve my problem

  6. baskar says:

    Cool, not going through finding out the same in Enterprisemanger console… crisp…

  7. Vitesh Tandel says:

    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?….

  8. deepak says:

    thanks

    your site is very good and helpfull, i have just used for sql server , that is excelent !!!!!

  9. Deghelt says:

    Thanks for your information ! it’s OK

  10. RUSSIAN says:

    Indian programming style sucks!

  11. Suprayogi says:

    Thanks, it is very useful and the most important is, it helped me on my works!

  12. vj says:

    Hiya

    Your example was clear and concise. Overall the steps you set out allowed me to follow through in creating and resolving the error that I had.

    Thank you very much.

  13. imron says:

    Terima Kasih ya…

  14. Shahzad says:

    Very nice explanation. Good buddy, its solved the issue

  15. Ashish says:

    Hi

    I got solution from here

  16. DotNetSucksAss Edwards says:

    I tried this and instead of the error in the tiele which I was getting before I got a primary key violation error instead, even though trhe 3 new keys I was entering were all new. Guessing thats a separate error though. Thanks for your help anyway.

  17. Antonio says:

    You save my life, thanks a lot!!!!

  18. Jay says:

    You can get simpler than this, excellent stuff

  19. girish says:

    i m using entity framwork and trying to insert object in entity but i m facing the same error ( mention blog for) and i m not clear how i can set IDENTITY_INSERT ON and OFF when i m using entity framework to insert records in sql server database.. can u plz help me out… thanks

  20. Mohamoud says:

    i have got that problem so that can you help me
    “An explicity value for the identiy colum in table reg can only be sepcified when column ls list is used and identity insert is on”

  21. Ramesh says:

    If you have already duplicate record in a table and you want to remove duplicate and also retain back the identity count as you want then

    IF NOT EXISTS(SELECT 1 FROM tableTest WHERE Name = ‘Test’ and Id = 7)
    BEGIN
    SET IDENTITY_INSERT tableTest ON
    –INSERT record in IDENTITY column with column list in INSERT
    INSERT INTO tableTest(id,name) VALUES(7,’Test’)
    –once you done, set value OFF to IDENTITY_INSERT
    SET IDENTITY_INSERT tableTest OFF
    End
    BR/
    Ramesh..

  22. lj says:

    Ur the best

  23. SET IDENTITY_INSERT edusync.[dbo].eventLog ON

    INSERT INTO edusync.[dbo].eventLog select * FROM res.[dbo].eventLog

    SET IDENTITY_INSERT edusync.[dbo].eventLog OFF

    I am Getting this error

    Msg 8101, Level 16, State 1, Line 1
    An explicit value for the identity column in table ‘edusync.dbo.eventLog’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s