I am about to write a custom SQL solution for data comparison between two data sources - old one is the existing production and new one with new changes. I am tasked to do the data comparison between the two. Here is some of the facts why I think I need to write custom solution:
1. Table name is different for some of the tables but column names are same
2. Table name is same but columns names are diff
3. Table name and column name is same but data type is different
4. table name, column name, and data type is different is different
We have SQL Data Compare tool which is awesome but if data type is different it cannot compare the data. So this is not an option.
What are my other options here? How about your experience?