none
turn Is Identity off and back on

    Question

  • Hi,

    I'm using the following to try and turn off the Is Identity property of a column;

    set identity_insert LOOKUP_CCLAS OFF

    the column is called CCLASS_PK

    I need to turn it off so I can isert a '-1' which I'm going to use as a flag for 'class not found'.

    I'd like to turn it on again after!

    When I use the SQL shown above, the property of the column is not changing and when manually adding rows the CCLASS_PK column is still behaving as if identity spec. is on.

    Any help MUCH appreciated, thankyou.
    Thursday, July 24, 2008 8:52 AM

Answers

  • Notice the name of the setting is IDENTIY_INSERT, not IDENTITY.  It means you are setting the ability to insert into an identity column on or off.  This is the opposite of what it looks like you were thinking. So, the pattern is:

     

    SET IDENTITY_INSERT table ON

    INSERT INTO table ...

    SET IDENTITY_INSERT table OFF

    Thursday, July 24, 2008 8:57 AM

All replies

  • Notice the name of the setting is IDENTIY_INSERT, not IDENTITY.  It means you are setting the ability to insert into an identity column on or off.  This is the opposite of what it looks like you were thinking. So, the pattern is:

     

    SET IDENTITY_INSERT table ON

    INSERT INTO table ...

    SET IDENTITY_INSERT table OFF

    Thursday, July 24, 2008 8:57 AM
  • Thankyou for such a speedy and helpful response, its now working just as you describe.
    Thursday, July 24, 2008 9:08 AM
  • Always glad to help.

    Thursday, July 24, 2008 4:12 PM