0

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?

Edited by Behseini: n/a

3
Contributors
5
Replies
6
Views
6 Years
Discussion Span
Last Post by debasisdas
0

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');
0

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

0

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:

Edited by Behseini: n/a

0

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.