I am trying to insert explicit values in one of my Real time Audit Production database. There are more than 80 columns, I have just taken here 5 columns. I am writing syntax is as below:

use MyDatabaseName

set identity_insert ObjectName.TableName on

insert into ObjectName.TableName ([col1],[col2],[col3],[col4],[col5]) 
  values ('value1','value2','value3','value4','value5') 

set identity_insert ObjectName.TableName off

After executing the above T-SQL statement, I am getting below mention error:

Msg 8101, Level 16, State 1, Line 44
An explicit value for the identity column in table 'ObjectName.TableName' can only be specified when a column list is used and IDENTITY_INSERT is ON.

I am sysadmin & db_owner. What is the problem?

  • Are any of the columns (i.e. col1 through col5) an IDENTITY field? If not, then why are you using SET IDENTITY_INSERT ON? – Solomon Rutzky Aug 11 '15 at 13:23
  • @srutzky, The First column is IDENTITY field. That's why using SET IDENTITY_INSERT ON. – Md Haidar Ali Khan Aug 11 '15 at 13:26
  • So, in your example code in the Question, you are saying that [col1] is the IDENTITY field? – Solomon Rutzky Aug 11 '15 at 13:27
  • 2
    Are there any Triggers defined on this table that you are inserting into? Your syntax seems fine (outside of putting single-quotes around 'value1', but that can just be due it being simplistic example code). – Solomon Rutzky Aug 11 '15 at 13:48
  • 1
    Yes, check for triggers, also please confirm that ObjectName.TableName is exactly the same (the trigger could be trying to insert into a similarly named table, but not identical). – Aaron Bertrand Aug 11 '15 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.