How to insert a column in a specific position in oracle without dropping and recreating the table?

Amit-

I don't believe you can add a column anywhere but at the end of the table once the table is created. One solution might be to try this:

CREATE TABLE MY_TEMP_TABLE AS
SELECT *
FROM TABLE_TO_CHANGE;

Drop the table you want to add columns to:

DROP TABLE TABLE_TO_CHANGE;

It's at the point you could rebuild the existing table from scratch adding in the columns where you wish. Let's assume for this exercise you want to add the columns named "COL2 and COL3".

Now insert the data back into the new table:

INSERT INTO TABLE_TO_CHANGE (COL1, COL2, COL3, COL4) 
SELECT COL1, 'Foo', 'Bar', COL4
FROM MY_TEMP_TABLE;

When the data is inserted into your "new-old" table, you can drop the temp table.

DROP TABLE MY_TEMP_TABLE;

This is often what I do when I want to add columns in a specific location. Obviously if this is a production on-line system, then it's probably not practical, but just one potential idea.

-CJ

Although this is somewhat old I would like to add a slightly improved version that really changes column order. Here are the steps (assuming we have a table TAB1 with columns COL1, COL2, COL3):

1. Add new column to table TAB1:

alter table TAB1 add (NEW_COL number);

1. "Copy" table to temp name while changing the column order AND rename the new column:

create table tempTAB1 as select NEW_COL as COL0, COL1, COL2, COL3 from TAB1;

1. drop existing table:

drop table TAB1;

1. rename temp tablename to just dropped tablename:

rename tempTAB1 to TAB1;

You (still) can not choose the position of the column using ALTER TABLE: it can only be added to the end of the table. You can obviously select the columns in any order you want, so unless you are using SELECT * FROM column order shouldn't be a big deal.

If you really must have them in a particular order and you can't drop and recreate the table, then you might be able to drop and recreate columns instead:-

First copy the table

CREATE TABLE my_tab_temp AS SELECT * FROM my_tab;

Then drop columns that you want to be after the column you will insert

ALTER TABLE my_tab DROP COLUMN three;

Now add the new column (two in this example) and the ones you removed.

ALTER TABLE my_tab ADD (two NUMBER(2), three NUMBER(10));

Lastly add back the data for the re-created columns

UPDATE my_tab SET my_tab.three = (SELECT my_tab_temp.three FROM my_tab_temp WHERE my_tab.one = my_tab_temp.one);

Obviously your update will most likely be more complex and you'll have to handle indexes and constraints and won't be able to use this in some cases (LOB columns etc). Plus this is a pretty hideous way to do this - but the table will always exist and you'll end up with the columns in a order you want. But does column order really matter that much?