| INSERTING DATA IN A TABLE FROM TABLES IN ANOTHER SCHEMA.. [message #18481] |
Fri, 01 February 2002 08:51  |
CATHYBEE
Messages: 20
Registered: January 2002
|
Junior Member |
|
|
SCHEMA1
lets say i have a table T1 with columns:
R1
R2
R3
SCHEMA2:
Then i have tables:
Table T2 WITH COLUMN
R1
TABLE T3 WITH COLUM
R2
TABLE T4 WITH
COLUMN R3.
I want to insert records into table T1 into rows R1,R2,R3 from tables T2.R1,T3.R2,T4.R3..
What is the best way of doing it?
I am assuming it would be using a procedure and then a cursor(or loop)? within that procedure..
Let me know the best way of doing it..
Also an example will be highly appreciated.
ALSO, IF THESE TABLES ARE IN DIFFERENT DATABASES INSTEAD OF DIFFERENT SCHEMA'S, HOW DO I MAKE CONNECTIONS BETWEEN 2 DATABASES..
|
|
|
|
| Re: INSERTING DATA IN A TABLE FROM TABLES IN ANOTHER SCHEMA.. [message #18513 is a reply to message #18481] |
Sat, 02 February 2002 21:35  |
Muthu
Messages: 58
Registered: August 2000
|
Member |
|
|
You have to use a database link for connecting to a different database.
This is the code required for it.
Create database link dblink1
connect to username identified by password
using 'tnsentry in the target database server where the dblink is getting created'
usage of database link:
insert into tablename@dblink values()
select * from tablename@dblink;
As far as your cursor procedure is concerned, it can be as follows:
create or replace cursor_data_transfer
declare
cursor mycursor is select R1,R2,R3 from T1;
myrow mycursor%rowtype;
begin
for myrow in mycursor
loop
insert into t2@dblink1 values(myrow.r1);
insert into t3@dblink1 values(myrow.r2);
end loop;
exception
when others then
dbms_output.put_line("Error");
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
