Merging Two Tables(From diffrent Database)Automatically)

[Behseini]

Hi Guys
I have to merge(insert)data from source database into target database(over 600 tables).The good thing is the both source and target tables have exactly same structure and constrains but I have to transfer only data that are not existing in target database/table.Right now I am using this code but as I told you there are over 600 tables and took lots of time to do it manually.

insert into Emp
(ID,SIN, Name, LName,  Age , YearHierd)
select ID,SIN, Name, LName,  Age , YearHierd
from Emp@SrcDatabase s
where not exists (select 1
from Emp d
where s.ID = d.ID
and s.SIN = d.SIN);

commit;

So, I thought I may make procedure to automate the transferring process but I am still challenging with it.
The very first thing is how I can list the columns name of a table from another database?
I use :

select column_name from cols where Table_name='Emp'

for the current database(target) but I need to get a list of coulmn name for same table in another database.I tried

select column_name from cols where Table_name='Emp'@SrcDatabase

which is not a correct syntax so can you please let me know how I can solve this?

[debasisdas]

use this

SELECT column_name FROM cols@SrcDatabase WHERE Table_name = UPPER('Emp');
or
select column_name from user_tab_cols@SrcDatabase WHERE Table_name = UPPER('Emp');

[crishjeny]

Merging Two Tables(From Diffrent Database)Automatically -- you need to make cursor , using cursor take data from one datasource and then fill data to another database

[Behseini]

Hi debasisdas, and crishjeny

Thanks for your reply I used something like this

insert into Emp
(
Select Column_Name From ALL_TAB_COLUMNS Where Table_Name='Emp'
Intersect
Select Column_Name From All_Tab_Columns@SrcDatabase Where Table_Name='Emp'
Order By Column_Name
)
Values (Select Column_Name From ALL_TAB_COLUMNS S Where Table_Name='Emp' 
where not exists (select 1
From Emp T
where S.ID = T.ID));

But I am getting following error

Error starting at line 23 in command:
insert into Emp
(
Select Column_Name From ALL_TAB_COLUMNS Where Table_Name='Emp'
Intersect
Select Column_Name From All_Tab_Columns@SrcDatabase Where Table_Name='Emp'
Order By Column_Name
)
Select Column_Name From ALL_TAB_COLUMNS s Where Table_Name='Emp'
where not exists (select 1
From Emp D
where s.ID = d.ID)
Error at Command Line:27 Column:65
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:

[Behseini]

Hi crishjeny,
Thanks for your comment but can you please let me know how I can do it?

[debasisdas]

but my question is if you want to merge data why are you looking into dictionary objects ?