kus2nd's mumbling

how to copy table from other database in oracle

In the past, I had difficulty in copying tables from other database both using oracle. I didn't know that it is just so simple, using this code:

copy from username1/passwd1@tnsname1 -
to username2/passwd2@tnsname2 -
insert username2.table2 (table2_column1, -
table2_column2, ..., table2_columnn) -
using select table1_columna, table1_columnb, -
..., table1_columnx -
from table1 where .... ;

note: tnsname1 is the source database, table1 is the source table, tnsname2 is the destination database, table2 is the destination table. How to make a summary table from many tables? From the above code, just change the query by adding other table name, the columns and how the tables joining or grouping is made. For example:

copy from username1/passwd1@tnsname1 -
to username2/passwd2@tnsname2 -
insert username2.summ_employee_sallaries (name, id, -
position, sallary) -
using select E.employee_name, E.employee_id, -
S.position_desc, S.amount -
from employees E, sallaries S -
where E.employee_position_id=S.position_id;

The above code shows that employee sallary depends on employee position (though usually in the real world same position doesn't mean you will get the same amount of sallary, right?). Remember, all destination columns should be included in the code, or it won't work.

posted by kus @ 8:08 AM