SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Identity Issues


    Have you ever received the following Error when developing your ETL or working with data cleanup?


    Msg 544, Level 16, State 1, Line 1

    Cannot insert explicit value for identity column in table 'tblDemo' when IDENTITY_INSERT is set to OFF.


    This is because the table has a column marked for identity. The best way around this is to use a table option called IDENTITY_INSERT. This will allow you to insert a row into a table including an identity value for troubleshooting, issue resolution, or reseeding the table.


    In order to do this, you need to enable IDENTITY_INSERT as demonstrated below:



    INSERT tblDemo(Table1PK, ColumnDescription)

    VALUES (3, 'First Row')



    Couple of things to point out about using IDENTITY_ INSERT:

    - It can only be enabled for one particular table at a time.

    - The user issuing the query must own the object in question.

    - You must specify the identity column value when this option is enabled.

    - If you are inserting a value greater than the current maximum value for that identity, the identity column will be reseeded with that new value.


    This is a VERY powerful option and can wreck havoc is used improperly. Be very careful if you are ever using this in production.


    No comments.

    Leave a Comment

    Please register or log in to leave a comment.