How to compare a table of SQL Server to a table of MySQL

Question

Recently we have ETL of all from MySQL to SQL Server. Obviously the datatypes have changed.

Is there any way to do detail quick data comparison (irrespective of datatypes) between MySQL and SQL Server table.

I understand that Except, checksum, hashbytes would also take data types into account.

I would just like to compare "abc" to "abc"

Thanks for your help.

Answer 1

I think you can use openrowset() in SQL Server to get data from a table or view in your MySQL database, then check its fields against your table or view in your current SQL Server database.
Something like this:

select *
from openrowset(N'MSDASQL',N'Driver={MySQL ODBC 5.1 Driver};Server=192.xxx.xx.xxx;Database=xxx_view;User=xxx_user27;Password=xxxx;Option=3;', 
    N'SELECT * FROM xxx_view.abc LIMIT 1000') mysqlAbcTable  
full outer join sqlserverAbcTable 
  on mysqlAbcTable.fieldAbc = sqlserverAbcTable.fieldAbc;

<< Here is a stream about this >>