Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    56

    Unanswered: Add column/update existing rows

    Hi,
    I need to add a column in SQL Server 2005 and then update the existing rows (600 million) with a default value. The column is char(1).

    What would be the best way to do this without causing alot of blocking during the update?

    Thanks

    Rebecca

  2. #2
    Join Date
    Aug 2010
    Location
    NYC
    Posts
    10
    I assume the database is being updated live?

    If you can bring it down for updating for a little while, you could this method

    1) Create a temp table:

    SELECT o.*, new_field_value as new_field_name
    INTO tmp_new_table
    FROM old_table o

    RENAME old_table to tmp_old_table

    RENAME tmp_new_table to old_table
    --------------------------------------------------
    Matt
    Case Culture - Fashion Cases for iPhone and BlackBerry

  3. #3
    Join Date
    Oct 2008
    Posts
    56
    Thats a very good idea but I won't be able to take the db offline during the update.

  4. #4
    Join Date
    Jan 2010
    Posts
    18
    Do you have an id column in there somewhere? Then you could update all the rows in a range until you've done them all. You'd need to do some timings to see how long the updates would take. And check to see if there is enough log space.

  5. #5
    Join Date
    Oct 2008
    Posts
    56
    I don't have an id column but i do have an int column. It isn't unique but i can update in groups based on that. That might be the best plan. Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •