table restructure

[nancy01]

I have a table that I am trying to restructure.
This table was designed 15 years ago and is not very discriptive.
I need to do three things:
One, create a new table with column_names that are descriptive,
Two, move the data from the old table over to new table and
Three, clean the data up since it has a lot of the fields have a lot of null values.
Any ideas on how to proceed?
thanks in advance

 

[carp]

For the first couple of tasks, I would just create a new table:

CREATE TABLE new_table AS SELECT bad_name1 good_name1, bad_name2 good_name2,.....,bad_name_n good_name_n FROM old_table;

This will create a new table with the descriptive column names and copy over the data. However, you will still have to recreate constraints and indexes. When that is done, you should be able to drop the old table, and then rename the new table:

RENAME new_table_name TO old_table_name;

Now you just have to scrub the data. How you do this will depend on what it is you need to do, and will probably involve a lot of updates/deletes.

 

[nancy01]

thanks for your response carp!
when you say: bad_name1 good_name1, bad_name2 good_name2,.....,bad_name_n good_name_n from old table
please what do you mean?
do you mean create new_table as select col1..coln from old table?
I don't want to assume so I wait for your invaluable response.

 

[nancy01]

sorry carp! I got it.
it takes me awhile but I always get it.
thanks for your help