Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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:

     

    SET IDENTITY_INSERT tblDemo ON

    INSERT tblDemo(Table1PK, ColumnDescription)

    VALUES (3, 'First Row')

    SET IDENTITY_INSERT tblDemo OFF

     

    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.

    Comments

    No comments.

    Leave a Comment

    Please register or log in to leave a comment.