-
insert from multiple tables
hi all
I need to insert data from two tables into one table, 15 colums from each table. In all 30 columns from both the tables.
Can anyone help me with the syntax ????
thankx
ICEMAN
-
stab1, stab2 >> into target
insert /* +append */
into target(15 columns separated by commas)
select col1, col2... from stab1;
insert /* +append */
into target(15 columns separated by commas)
select col1, col2... from stab2;
HTH.
-
If you can able to join these two tables basing on some columns then it is easy ; otherwise one way I know is :
SQL> desc table1;
Name Type
----------------------------------------- ----------------------
A VARCHAR2(10)
B VARCHAR2(10)
SQL> select * from table1;
A B
---------- ----------
A1 B1
A2 B2
SQL> desc table2;
Name Type
----------------------------------------- ----------------------
C VARCHAR2(10)
D VARCHAR2(10)
SQL> select * from table2;
C D
---------- ----------
C1 D1
C2 D2
SQL> create table table3 as select a,b,Null C,NULL D from table1 UNION select NULL a, NULL b, C,D from table2;
Table created.
SQL> desc table3;
Name Type
----------------------------------------- ----------------------------
A VARCHAR2(10)
B VARCHAR2(10)
C VARCHAR2(10)
D VARCHAR2(10)
SQL> select * from table3;
A B C D
---------- ---------- ---------- ----------
A1 B1
A2 B2
C1 D1
C2 D2
-
Hello iceman,
Does both the tables have any common column on which you can join.
If you have then its easy to insert into another table.
Example:
Table: XYZ
Columns: a number,
b number
Table: ABC
Columns: x number,
y number
and columns 'a' and 'x' are common then
Table: TEMP
columns: a number,
b number,
x number,
y number
insert into temp
select a.a, a.b, b.x, b.y
from xyz a, abc b
where a.a = b.x;
I hope this help you.
Thanks,
________________
ShanDJ
-
hi there
yes there is a common column in the two tables.
regards
ICEMAN
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|