Re: Inserting columns into existing table

Oracle has no problem doing this if you give the new field a name.

CREATE TABLE newtab AS

   SELECT a, ' ' NEWCOL, b
   FROM oldtab;

The field type will be defined by the example. And likely you will get a VARCHAR2 rather than a CHAR which shouldn't matter, but if it does, can be easily dealt with using ALTER TABLE.

Daniel Morgan

Third_of_Five wrote:

> I am trying to do this
>
> create table osec.a_table (
> a char(1),
> b char(1)
> );
>
> create table osec.b_table (
> a,
> c char(1),
> b
> ) as select
> a, ' ', b
> from osec.a_table;
>
> i.e. create a copy of a table with a new column inserted in the field
> sequence. This is quite important because of the way our software
> talks to the database.
>
> I know the above create table as select is not legal in oracle, but am
> wondering if there is anyway in oracle (really needs to be in
> SQL/sqlplus) to achieve what I want.
>
> Cheers
> Austin
Received on Fri Apr 12 2002 - 13:29:33 CDT