Compare data from MS SQL and MySQL databases

Question

I'd like to compare 2 queries results in order to get non corresponding data.

I have a MS SQL server that is linked to a MySQL server.

My first query get the emails and the number associated to it on MS SQL database:

select email, number  from [DB].[dbo].[Contacts];

My second query get the emails and the number associated to it on MySQL database:

select email, number from database.contacts;

I know that I can run a query on MySQL from MS SQL by using this kind of query:

SET @SQLStr = 'SELECT * FROM OPENQUERY([DB-01],''select email, number from database.contacts'')'
EXEC(@SQLStr)

But I have no idea of how to read and compare the results.

I'd like to know if email and number association existing in MS SQL database is different in MySQL server.

i.e.: if email = '[email protected]' and number = 00222 on MS SQL but email '[email protected] and number = 012325 on MySQL, I want to get the second result that is different.

What comparison do you want to do, and what is the expected output, if any? — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 27, 2019 at 10:15

Michał Turczyn · Accepted Answer · 2019-02-27 10:21:00Z

1

Why using dynamic SQL when there's nothig dynamic about it?

You can use standard query:

SELECT * FROM OPENQUERY([DB-01],'select email, number from database.contacts')

With your SQL Server query you'll get two resultsets.

To compare results you could use EXCEPT, to get records from one table not existing in another table.

Other way is to use OUTER JOIN~and see if any records haveNULL`s meaning that there is no match in the other table.

answered Feb 27, 2019 at 10:21

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Compare data from MS SQL and MySQL databases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related