export with query

[Vogra]

Hi All!
I need to insert into database in specific schema, some data from other instance with the same schema name. Can anyone help me with script? Like:
Insert into SCHEMAPROD.PFUNFPTO@produc
select *
from SCHEMAPROD.PFUNFPTO@devel
Where fnd = '000000000002009';
It is correct? I can use dblink?
Thanx.

[Kawah Cheung]

Hi,

What you have should do the trick, if the tables already exist in both databases. DBlink is fine if you want to do this for a couple of tables. If you plan on doing this for a lot of tables, then export/import may be the way forward.

Rgds,
Kawah

[Jeanine Kone · Accepted solution]

Just to note, you do not need the @database for the table on the database you are actually connected to.

For example if you are logged into schema@proddb and want to get data from schema@devldb then you can:

insert into schema.table
select * from schema.table@devldb
where field = value;

[Kawah Cheung]

Hi,
On the PRODUC database:

create public database link
connect to identified by "" using ''
/

Connect to the SCHEMAPROD schema on the PRODUC database. Use the following if the tables already exist:

Insert into PFUNFPTO
select * from PFUNFPTO@devel
Where fnd = '000000000002009'
/

If the database link is created correctly, that is all that is required.

Rgds,
Kawah