Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

compare two tables from different databases with diferent column names

Can any one help me out to compare the data between two tables where the tables are sitting on different databases with different columns

Ex:   'TB1' table in in 'DB1' sitting on server A,  with  'TB2' table in in 'DB2' sitting on server B
0
bhanu823
Asked:
bhanu823
  • 4
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
try a minus query and database link:

select col1, col2 from DB1_table
minus
select colz, colx from DB2_table@serverB
/

Then the reverse:
select colz, colx from DB2_table@serverB
minus
select col1, col2 from DB1_table
/


alternative: spool the data to a text file form both tables and do a diff.
0
 
bhanu823Author Commented:
WHY DO WE NEED TO DO IT FOR  2 TIMES?.. NORMAL AND THEN REVERSE?
0
 
slightwv (䄆 Netminder) Commented:
a Minus will also show 'missing' rows.  You need it twice for rows in one, not in the other and visa versa.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
awking00Commented:
You say the column names are different, which is fine using minus, but you also need to be sure they are the same datatype and in the same order.
0
 
slightwv (䄆 Netminder) Commented:
>>WHY DO WE NEED TO DO IT FOR  2 TIMES?..

Create a simple test case and see.    using the case below, if you only run it once you will not get the 'f' row.


drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

drop table tab2 purge;
create table tab2(colz char(1), colx char(1));

insert into tab1 values('a','a');
insert into tab2 values('a','a');

insert into tab1 values('b','c');
insert into tab2 values('b','d');


insert into tab1 values('e','e');
insert into tab2 values('f','f');
commit;

select col1,col2 from tab1 minus select colz,colx from tab2;
select colz,colx from tab2 minus select col1,col2 from tab1;

Open in new window

0
 
awking00Commented:
If you want to issue one query -
select col1, col2, ... from tab1
union all
select colz, colx, ... from tab2
minus
(select col1, col2, ... from tab1
 intersect
 select colz, colx, ... from tab2)
0
 
slightwv (䄆 Netminder) Commented:
>>If you want to issue one query -

If you do this you will likely need to add some designator to determine what rows came from what side.

For example, using the example above:  Does 'e' not exist in DB1 or DB2?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now