I have found that the Data Comparison tool is not always accurate. Has anyone come across this issue?
I was comparing 2 tables on two different servers. Both tables have the same schema. When I did a compare, the results show that there are records existing in just the source as well records existing in just the target.
When I did a manual compare they seem to match on the key. To make sure that the key really match (i.e. no white whitespaces, etc.) I exported them to EXCEL and did a cell by cell comparison on the key on two records (one from source, one from target) that should have a matching key. Everything matches on the key.
I was still not sure why they didn’t match on the key, so I extracted the table from both servers and wrote a query in SSMS to do a compare using the “key” as my join condition. They matched 100%. This tells me that Toad is not always accurate when it comes to data comparison. The records should be displayed record under “Different” in TOAD.
Any suggestions as to why this is so? In my comparison in Toad I ignore everything I can ignore (whitespaces, case, timestamp, etc.)