Solved

compare two tables from different databases with diferent column names

Posted on 2012-04-09
7
499 Views
Last Modified: 2012-05-01
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
Comment
Question by:bhanu823
  • 4
  • 2
7 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37823815
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
 

Author Comment

by:bhanu823
ID: 37823825
WHY DO WE NEED TO DO IT FOR  2 TIMES?.. NORMAL AND THEN REVERSE?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823835
a Minus will also show 'missing' rows.  You need it twice for rows in one, not in the other and visa versa.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:awking00
ID: 37823841
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823851
>>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
 
LVL 32

Expert Comment

by:awking00
ID: 37823908
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37823948
>>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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question