Adding new column fields into a big table issue

I have an existing table which has about 70 columns with 3 million rows in it. I was asked to add additional 50 new columns into the table. I have tried to add them in through the Enterprise manager design table but experiencing some problems. The adding process seemed never going to be end. Is there any good efficient way to do it? I appreciate the help!

J8You're trying to add 150 million pieces of data (3 million rows times 50 columns) to your table, which now has 210 million pieces (3 million rows times 70 columns). That is significant growth (about 70%), so it will probably take quite a while.

If the columns are all NULL-able, then I'd create a second "child" table that had the primary key from the first table and all of the new columns in it. That would allow you to populate them much more gracefully, possibly in stages.

If you really need to add these columns to the existing table because of Referential Integrity issues or due to other reasons, then I'd strongly recommend declaring downtime (so you can force the users off of the system), then making the changes from Query Analyzer using the ALTER TABLE command. It still won't be fast, but it will be faster than any other method for doing this kind of job.

-PatP|||Pat,

For whatever reason, I have to stick those new columns into this big table. You can consider this table as kind of 'feed' table.

Thanks for the tips.

J8|||You should not have problems adding 50 columns providing they all allow NULL. If not then they must have a default, and you may bring your database down very easily (which what I suspect has happened) because every row must be updated with default value for that column or columns.|||You can script the addition of columns and run it from query analyzer

alter table mytable add col71 int null, col72 int null, ...

as long as all of the columns are nullable this should happen instantly. I would expect EM to add them instantly too unless there is some difference between the default ANSI settings of the DB, Table, and your EM Session.