Post reply

Change property of column

  • May 17, 2006 at 3:15 pm

    #172704

    Hello,

    I would like to use T-SQL syntax to add the IDENTITY property to an existing column in a table.

    After reading BOL, I am unclear whether this is possible or not.  I tried:

    ALTER TABLE mytable  ALTER mycolumn int IDENTITY

    and variations of the above. 

    Is this quest possible?

    TIA,

    Elliott

     

  • May 17, 2006 at 3:22 pm

    #638578

    not without a LOT of work.


    * Noel

  • May 17, 2006 at 3:32 pm

    #638584

    WORK!!? 

    OK, I'll abandon this approach!

    Thanks

  • May 17, 2006 at 3:42 pm

    #638588

    If there are not many tables linked to this one you can do it with EM and it won't be a LOT of work ... for you at least ....

    but it entails the saving of the table to a temporary one dropping the exsisting, creating a new one and adding the records from the temp with identity_insert  set to on

    The reason I said a lot of work is that I like to know what actually is going on just not get any surprises from things like "EM"

    Cheers,

     


    * Noel

  • May 17, 2006 at 4:08 pm

    #638595

    Noel,

    Actually, in my brief testing, I was able to add the "identity" property to a column in one move in EM (If I remember correctly).

    But that wasn't the goal.  This is really about a daily job of truncating a table and re-filling it with values obtained from a view.  Being lazy, I wanted to do the insert without listing all the column names.  Turns out that using the technique "INSERT_IDENTITY ON" reqjuires the use of a column list if there is an IDENTITY column involved.  (According to BOL and my testing).

    So my evil plan was to write a proc to truncate the daily table, remove the IDENTITY property, fill the table without using a column list, and finally re-install the IDENTITY property on the existing column.  (In case other activities added records to the daily table).

    Not today, evidentally

    Elliott

  • May 17, 2006 at 4:12 pm

    #638598

    EM makes it look like one move but it really isn't

    And as you see it's a LOT of work

     


    * Noel

  • May 18, 2006 at 5:59 am

    #638671

    I think it might be the lazier approach to just list out the columns.

    If you don't have a bunch of indices or relationships based on the IDENTITY column, you can always just drop the column, load your data, then add an IDENTITY column back to the table. SQL Server will automatically fill in the values.

Viewing 7 posts - 1 through 6 (of 6 total)

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