cancel
Showing results for 
Search instead for 
Did you mean: 

export with query

SOLVED
Go to solution
Vogra
Regular Advisor

export with query

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.
We are spirits in the material world
3 REPLIES
Kawah Cheung
Advisor

Re: export with query

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
If in doubt, ask!
Jeanine Kone
Trusted Contributor
Solution

Re: export with query

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
Advisor

Re: export with query

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
If in doubt, ask!