Posted By

stews on 09/17/08


compare Oracle Asktom

Versions (?)

Efficiently compare columns from two tables

 / Published in: PL/SQL

This compares selected columns between 2 tables that you think might have differences. It lists the rows and values that are different between the two sources

You can add WHERE clauses as appropriate. You could also use this to compare the results of two queries.

This is from Tom Kyte ( I can't say I understand exactly how it works, but it's much faster than other methods I've seen. It has the advantage of only looking through each table once.

  1. SELECT COUNT(src1) AS in_old_addresses,
  2. COUNT(src2) AS in_new_addresses,
  3. first_name,
  4. last_name,
  5. city
  6. FROM (SELECT first_name, last_name, city, 1 AS src1, TO_NUMBER(NULL) AS src2
  7. FROM old_addresses
  8. SELECT first_name, last_name, city, TO_NUMBER(NULL) AS src1, 2 AS src2 FROM new_addresses)
  9. GROUP BY first_name, last_name, city
  10. HAVING COUNT(src1) <> COUNT(src2);

Report this snippet  

You need to login to post a comment.