Data Migration - Verify Data loaded where Primary Key can change

Question

I am currently trying to write SQL to verify the counts of the data that has been migrated from one application to another.

One of the main tables that is being migrated sometimes contains a primary key that already exists in the target application so it needs to be changed. This results in my counts not matching up.

I have a reference table for these changed primary keys but I'm not sure how to incorporate this reference table into my left join.

I really don't know how to include the condition where the key from Table A could be the key on Table B or the new key stored on the Reference table?

select count(*)
from table_b b
  left join table_a a on
            b.key = a.key
  where a.key is null;

The reference table is really simple, two colmumns, old_number, new_number. It will only contain entries where the key in table A needed to be changed before being loaded into table B.

old_number, new_number
12345678, 13345678
23456781, 24456781

How can I include this scenario?

select count(*)
from table_b b
  left join table_a a on
            b.key = (a.key or new_number if it exists)
  where a.key is null;

So, if the query can include the new_numbers in the reference table then the migration count should match the count in Table A.

Answer 1

This should work

select count() from table_b b, table_a a where b.key = a.key UNION select count() from table_b b, reference_table re where b.key = re.new_number;