SQLTeam.com | Weblogs | Forums

Sum of three column and delete old one


#1

Dear Expert

I have table in SQL with 20 column which having amount only, now I want to reduce it by 10, but my data too huge to down load in excel and upload it again,
so I need to create one column which is sum of three old column and then delete that 3 column, and from onward I will upload data as per new table. for example
i have three old column CL1, CL2, CL3 with some value, now I have to create 4th column CL4 which is the sum of (CL1+CL2+CL3) and after that remove all three old column.


#2

If the table is huge, this could take a long time and alot of processing. One approach would be to create a new table with the structure you want, then insert from the old table and then rename the old table and rename the new one to the old table name. Things to keep in mind are current indexing will probably still be needed, as well as anything accessing this table will need to be changed since those columns no longer exist. Also, depending on how volatile the table is, there could be contention and you might need to catch up once you rename (i.e records might be inserted/updated during insert). Once the switch has been completed and confirmed, you can drop the original table