Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

compare two tables from different databases with diferent column names

Posted on 2012-04-09
7
Medium Priority
?
503 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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1500 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 78

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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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 78

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 78

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

783 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