0

I am trying to compare two tables in two separate databases (same instance) for a new data extract The tables have around 4 millions rows and due to how they've been extracted are not in the same order in each table, as they are just staging tables (there is no PKs).

I have tried using (select * from table 1 Except select * from table 2) UNION ALL (select * from table 2 Except select * from table 1) however this results in around 10 millions rows so is not working as expected.

I would guess the results should be around a few thousand.

QueryQuirk
  • 3
  • 1

2 Answers2

0

First of all you have to define your primary key - one or several fields what will unique identify the record. All fields as primary key is possible but not recommended

Second, you have to decide what you mean as 'compare':

  • records exists in first table but not in second
  • records exists in both tables but has difference in non primary key fields
  • records exists in the second table only
SergeyA
  • 1,522
  • 1
  • 5
  • 9
0

If the records are unique in each table you can do something like this:

SELECT COUNT(*), FIELD1, FIELD2, FIELD3 FROM (
SELECT FIELD1, FIELD2, FIELD3 FROM TABLE1 
UNION ALL 
SELECT FIELD1, FIELD2, FIELD3 FROM TABLE2) SUBSEL
GROUP BY FIELD1, FIELD2, FIELD3
HAVING COUNT(*) = 1
Fausto Galati
  • 16
  • 1