Turn IDENTITY on and off?

  • Folks,

    Is it possible to turn the IDENTITY characteristic of an identity field on and off using ALTER TABLE and ALTER COLUMN?

    I would like do this to a bunch of columns on various tables created with SELECT...INTO queries.  I want to "turn off" the identity characteristic, update the values, and then turn it on again.

    Alternatively, I know I can drop the column, and then make a new one -- but I would like to create the new one in the same ordinal position as the old one.  Is that possible?

    What I want, with the minumum fuss and simplest possible coding, is to be able to take any table with an arbitrary field structure, turn off its identity column, change that field's values, and turn it on again, without changing its column order.  I won't know the structure of the table before hand -- so enumerating the columns for SELECT...INTO and INSERT ... SELECT...  statements would be quite painful to code, and is a route I would like to avoid.

    Am I out of luck, dear friends?  😉

    - Tom

  • From BOL...

    SET IDENTITY_INSERT

    Allows explicit values to be inserted into the identity column of a table.

    Syntax

    SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }



    Shamless self promotion - read my blog http://sirsql.net

  • Thanks stacenic,

    I thought of that, too, but it doesn't solve my problem.  I don't want to INSERT new rows into the table, I want to change existing values. 

    Turning SET IDENTITY_INSERT to OFF doesn't not allow you to update the existing values.  It only allows you to control what new values will be for newly inserted rows.

    - Tom

  • Try:

     

    ALTER TABLE tablename

    NOCHECK CONSTRAINT constraint_name

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply