Create one tables from 2 different tables

lesak

Hi,

How I can create one table from 2 different tables. Source tables have data and I want to include it in new table.
I try this:

create table NEW_ONE
select * from OLD_ONE
union
select * from OLD_ONE2;

But it didn't work correctly

Robert Geier

Maybe you mean this ? Not sure what your join condition is ?

create table NEW_ONE as
select o1.id_oo, o1.name_oo, o2.id_002, o2.name_oo2
from old_one o1, old_one2 o2
where o1.id_oo = o2.id_oo2; -- PLEASE CHECK THIS JOIN CONDITION

Robert Geier

What is the error you get ?

The correct syntax would be :-

create table NEW_ONE as
select * from OLD_ONE
union
select * from OLD_ONE2;

lesak

I don't have any error. This syntax create table NEW_ONE, but this table have columns only from OLD_ONE table There aren't any column from OLD_ONE2 Any suggestions?

I don't forget about "as" in my query, only in this post.

Edited by: tutus on Sep 8, 2008 6:36 AM

Robert Geier

I think maybe you want to join tables OLD_ONE and OLD_ONE2 (e.g with "="), not union ?

Start by using select to get the data you want in sqlplus, then when the select statement works you can add the "create table NEW_ONE as".

lesak

No no. For explain:

OLD_ONE columns:
* id_oo
* name_oo

OLD_ONE2 columns:
* id_oo2
* name_oo2

Table NEW_ONE should have this columns:
* id_oo
* name_oo
* id_oo2
* name_oo2

And data from both of this tables.

Robert Geier

Maybe you mean this ? Not sure what your join condition is ?

create table NEW_ONE as
select o1.id_oo, o1.name_oo, o2.id_002, o2.name_oo2
from old_one o1, old_one2 o2
where o1.id_oo = o2.id_oo2; -- PLEASE CHECK THIS JOIN CONDITION

lesak

Yes, now it works Thanks.