0

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.

mcquaim
  • 159
  • 7
  • 15
  • Are you saying number mentioned in old_number column is replaced with new_number in target table? – Mansi Raval Aug 14 '19 at 11:09
  • Hi there. Yes, that is it exactly. The old_number belongs in table A and the new_number belongs to table B. – mcquaim Aug 14 '19 at 11:13
  • How do you change the number in table B if the constraints PK violated? Is there any specific logic? – Mansi Raval Aug 14 '19 at 11:15
  • The tables are not linked. This is a migrations project, taking data from one application to another. So, loading data with a primary key in table A into a table in the new application where sometimes the primary key has already been used. When this is the case they are assigned a new primary key and a record added to the reference table with the old and new key number. I am trying to write SQL to verify the loads were successful... – mcquaim Aug 14 '19 at 11:18
  • what if old number and new number is same? in that case reference table will have entry or not? – Mansi Raval Aug 14 '19 at 11:20
  • Sorry, I should have stated that. If the key doesn't exist in table B it is inserted as is and no entry is added to the reference table. It only holds entries for the keys that had to be altered. – mcquaim Aug 14 '19 at 11:22
  • 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; – Mansi Raval Aug 14 '19 at 11:23
  • Bingo, the solution is always a lot simpler than you initially imagine lol... Thanks for the help Mansi. – mcquaim Aug 14 '19 at 11:33
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/197924/discussion-between-mansi-raval-and-mcquaim). – Mansi Raval Aug 14 '19 at 11:39

1 Answers1

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;

Mansi Raval
  • 371
  • 6
  • 14