locked
cannot insert explicit value for identity column in table when identity_insert is set to off RRS feed

  • Question

  • User-266805086 posted

    when execute insert query through SP, I am getting below error. I have tried to do "ON" but after then it is also there.

    "cannot insert explicit value for identity column in table when identity_insert is set to off"

     

    Tuesday, June 12, 2012 9:28 AM

Answers

  • User1999579388 posted

    Hi yatinpatel13983,

    You could set identity_insert your_tablename ON to insert identity column value. There is an example using set indentity_insert in your query, it will give your some point to solve your problem.

    Please see: http://msdn.microsoft.com/en-us/library/ms188059.aspx

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 18, 2012 4:19 AM
  • User-1142685999 posted

    try this:

    SET IDENTITY_INSERT YourTableName ON
    GO
    
    -- insert an explicit ID value here
    INSERT INTO YourTableName (ID, Column1) VALUES (11, 'Column Value')
    GO
    SET IDENTITY_INSERT YourTableName OFF
    GO



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 18, 2012 4:33 AM

All replies

  • User-2010311731 posted

    This means that the table you are inserting into has an auto-incrementing primary key defined.  You need to remove this field from your insert statement.

     

    Matt

    Tuesday, June 12, 2012 9:37 AM
  • User79986525 posted

    Hi ,

    Leave auto incremented field (the column that you set as primary key ) in insert query.it will automaticaly take that field.

    Tuesday, June 12, 2012 9:40 AM
  • User-794985634 posted

    The column is auto-increment. So it will not allow to insert the data into that column. Donot pass any data to that column or simpy leave that column while inserting data.

    Tuesday, June 12, 2012 1:02 PM
  • User1999579388 posted

    Hi yatinpatel13983,

    You could set identity_insert your_tablename ON to insert identity column value. There is an example using set indentity_insert in your query, it will give your some point to solve your problem.

    Please see: http://msdn.microsoft.com/en-us/library/ms188059.aspx

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 18, 2012 4:19 AM
  • User-1142685999 posted

    try this:

    SET IDENTITY_INSERT YourTableName ON
    GO
    
    -- insert an explicit ID value here
    INSERT INTO YourTableName (ID, Column1) VALUES (11, 'Column Value')
    GO
    SET IDENTITY_INSERT YourTableName OFF
    GO



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, June 18, 2012 4:33 AM