Post reply

Altering a table to add an identity

  • May 23, 2003 at 10:10 am

    #81629

    Can't find what is wrtong with this statement, any ideas ?

    ALTER TABLE CAN_MARKET_DATA ALTER COLUMN MRKT_DATA_SEQ [int] [identity(1,1)]

  • May 23, 2003 at 11:56 am

    #458400

    Neither SQL Server 2000 nor SQL Server 7.0 supports adding the IDENTITY property to an existing column by using a single T-SQL command such as ALTER TABLE <table_name> ALTER COLUMN <arguments>. However, you can add the IDENTITY property to an existing column by using Enterprise Manager.

  • May 23, 2003 at 2:29 pm

    #458416

    If this is a new column, you can modify your statement to be

    ALTER TABLE CAN_MARKET_DATA ADD MRKT_DATA_SEQ [int] [identity(1,1)]

    I don't know how to change an existing column.

    Guarddata-

  • May 23, 2003 at 2:52 pm

    #458420

    Adding the IDENTITY property to an existing column actually goes by creating a new table with same structure as original table with identity property defined, copy data from original table, drop orginal table, rename the new table to the original table and extra works as rebuiding indexes/keys.

    You can find out exactly statments by running Profiler when making changes through EM.

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

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