0

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.

shA.t
  • 16,580
  • 5
  • 54
  • 111
suvi
  • 3
  • 1

2 Answers2

0

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 >>

shA.t
  • 16,580
  • 5
  • 54
  • 111
0

According to me there are two ways to achieve this-:

Manual Comparison -: You can check the number of records in mysql & sqlserver tables and for some subset of data You can compare the data between MySQL & SQL Server Databases. Or You can download samples of data from Source & Target in Excel and use Macros for data Comparison & Validation.

Using DB comparison tools -: There are some DB comparison tools like Query Surge, BI Validator, DB Solo etc. available for data comparison. Using these tools, you can compare data between two different databases like Mysql & sql Server in your case. These tools can provide upto 100% data coverage.

SiriA
  • 21
  • 4