Post reply

How to make a column an IDENTITY using ALTER COLUMN?

  • August 23, 2005 at 7:34 pm

    #168135

    Hi,

    Does anyone know how to make a column an IDENTITY using T-SQL

    I tried the following but it gives an error:

    ALTER TABLE table1

    ALTER COLUMN column1 INT IDENTITY (1,1) NOT NULL

    Thanks,

    Mo

  • August 23, 2005 at 7:53 pm

    #584443

    If the column already has data, then you can't change it to an Identity.

    If you can clear the table, then you can alter the column to an identity. If the existing data already has id values for that column, when you reload data you'll need to set identity insert on.

    If you can't clear the table, you'll need to add another column as in identity. Then you can remove the old column and rename the new one.

     

    --------------------
    Colt 45 - the original point and click interface

  • August 23, 2005 at 8:25 pm

    #584450

    Can't you make a new Identity Column.  SET IDENTITY_INSERT tablename OFF.  Then insert the existing data and SET IDENTITY_INSERT tablename ON?    (This is assuming the data is actually consistent with an Identity Column). 

    I wasn't born stupid - I had to study.

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

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