Inserting columns in a table

[oradba101]

Good morning, all

I need to insert some columns at a certain position in a table. If I add the columns they are always put at the end of the table whereas I need to be able to select where thay need to be added. Any help would certainly be appreciated.

Regards,


William Chadbourne
Oracle DBA

 

[KenCunningham]

William, could you create a temporary table with the columns in the order you require, populate it with the data from the original then drop the latter and replace it with the new one?

I don't mind people who aren't what they seem. I just wish they'd make their mind up.

 

[oradba101]

Hi, Ken

Since this is relatively new to me can you provide detailed instructions on how to do this?

Regards,


William Chadbourne
Oracle DBA

 

[KenCunningham]

William, sorry, just about to leave for the weekend, but this:

http://orafaq.com/faq/how_does_one_add_a_column_to_the_middle_of_a_table

will hopefully help you. Have a good weekend!

I don't mind people who aren't what they seem. I just wish they'd make their mind up.

 

[SantaMufasa]

Here's an example of adding a column wherever you want it:

select * from william1;

   ID NAME
----- --------------------
    1 North America
    2 South America
    3 Africa / Middle East
    4 Asia
    5 Europe

SQL> create table william2 as select id, 'x' new_col, name from william1;

Table created.

SQL> alter table william2 modify new_col varchar2(10);

Table altered.

SQL> drop table william1;

Table dropped.

SQL> rename william2 to william1;

Table renamed.

SQL> desc william1
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------
 ID                                        NOT NULL NUMBER(7)
 NEW_COL                                            VARCHAR2(10)
 NAME                                      NOT NULL VARCHAR2(50)

SQL> select * from william1;

        ID NEW_COL    NAME
---------- ---------- --------------------------------------------------
         1 x          North America
         2 x          South America
         3 x          Africa / Middle East
         4 x          Asia
         5 x          Europe

Note that NEW_COL contains an 'x' in all rows. You can now UPDATE the contents of NEW_COL with whatever you want, including NULL values. Also note that the "alter table william2 modify new_col varchar2(10);" command expands the maximum width of NEW_COL from varchar2(1) to whatever maximum width you please (varchar2(10) in my example).

Let us know if this answers your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[oradba101]

Hi, Mufasa

I'll give it a try in Tuesday and let you know.

Regards,


William Chadbourne
Oracle DBA