Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
J827
Starting Member
4 Posts |
 Posted - 2004-08-13 : 13:40:35
|
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 view but experiencing some problems. The process seemed never going to be end. Is there any good efficient way to do it??? I appreciate the help! J8 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-13 : 13:47:26
|
| ALTER TABLE ADD COLUMN...But why does your table have so many columns?Tara |
 |
|
|
J827
Starting Member
4 Posts |
Posted - 2004-08-13 : 14:30:44
|
| Tata,Thanks for the tip!You can consider this big table as a kind of 'feed' table with bunch of different properties.J8 |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-13 : 15:43:07
|
| i would seriously consider the db design on this table if i were you. you're just begging for something to go wrong.even the maintainance is hard work on that many columns.Go with the flow & have fun! Else fight the flow :) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-13 : 17:00:59
|
| Just as an fyi, many times instead of trying to alter the table, Enterprise Manager does this:1. Create temp table to mirror old table.2. Copy old data to temp table.3. Delete old table.4. Create new table.5. Copy temp data to new table.6. Delete temp table.You can imagine why it takes FOREVER. :) Do you mean it's just a temporary staging table to, for example, feed mainframe data or data reporters to a normalized data model?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-08-13 : 23:06:46
|
| N O R M A L I Z E !!!!!!!!if you really need to add all these columns (doubtful), why don't you just create a new table, with a 1-1 relation to the origianl table? join them together in a view and no one will know the difference. this also will avoid issues like rows becoming too big (>8KB)- Jeff |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-15 : 07:39:37
|
| "many times instead of trying to alter the table, Enterprise Manager does this"In my experience it won;t do that if you only APPEND columns, but if you insert one in the middle, or "tinker" with the properties of an existing column, then it definitely will.Also, if you set one of the columns to NOT NULL and apply a DEFAULT then basically SQL has to do an UPDATE of all existing rows to get the default value into them. That takes time AND log space.You might be better off using Enterprise Manager to let you "visually" add the new columns, and then using the Make Script button to show you what ALTER TABLE statement EM is going to use - then either cut&paste that to a script of your own (you could allow the column to be NOT NULL and manually set the initial default value "in batches" so it doesn;t take too long, or too much resource), or if you are happy with the script you could just let EM go ahead.Kristen |
|
|
|
|
|
|
|
|
Adding New Column fields into a big table Issue