If there a way I can inner join a MS Sql table to a MySql Table in one query using MySql?

Question

I have 2 servers one servers runs Microsoft SQL Server and the other one is using MySql.

I need to be able to inner join a table from MS SQL name it "A" to a table "B" located on a different server that uses MySql

So I want to be able to do something like this

SELECT A.*, B.* FROM A INNER JOIN B ON A.id=B.id LIMIT 100

How can I do this? note that both servers are on the same network.

You might be able to accomplish that with ODBC, but neither m(y|s)sql can talk to each other directly. — Marc B
– Marc B, Commented Apr 25, 2013 at 16:54
You could use a linked server from SQL Server, but they often have issues with performance and data type compatibility so it may not be an ideal solution. And it would mean running the query on SQL Server, not MySQL, which is the opposite of what you wanted (but perhaps you have some flexibility). — Pondlife
– Pondlife, Commented Apr 25, 2013 at 16:56
Thanks guys for your feedback. @Pindlife, yes I don't think this will work because I am using MySql for everything else. Marc B, How can I use the ODBC to do that? can you please help me with an example? — Mike
– Mike, Commented Apr 25, 2013 at 16:59
Where up to i Know Best way for such cases to use some Scripting Language(PHP\ASP) etc. between two different RDMBS, to produce Pseudo JOINs — Muhammad Haseeb Khan
– Muhammad Haseeb Khan, Commented Apr 25, 2013 at 17:01
@MuhammadHaseebKhan I am using PHP to generate my reports. But how can I pull the data from both sources? — Mike
– Mike, Commented Apr 25, 2013 at 17:04

RandomUs1r · Accepted Answer · 2013-04-25 17:06:29Z

1

1st link on google states...

you need to install this:

http://www.mysql.com/products/connector/

and follow this guide:

http://technikhil.wordpress.com/2007/05/13/getting-microsoft-sql-server-and-mysql-to-talk/

to link up the servers and then use openquery to execute MS SQL queries.

answered Apr 25, 2013 at 17:06

RandomUs1r

4,2182 gold badges27 silver badges47 bronze badges

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

7 Comments

Muhammad Haseeb Khan Over a year ago

you might have to face lot of compatiability issues

RandomUs1r Over a year ago

why would OpenQuery have issues? Reading above it sounds like he's using php, which I would probably use over this approach, though I'd still consider architecture.

Mike Over a year ago

I am USING PHP yes. But how can I use PHP to connunicate to servers? I am using PDO to connect to my mysql server

Mike Over a year ago

@RandomUs1r I have installed MySql ODBC Driver but in the second think it show a code to connect to mysql server. I am trying to connect to SQL server. how can I do that? how to I change this code to connect to SQL server from MySql EXEC master.dbo.sp_addlinkedserver server = N'MYSQL', srvproduct=N'MySQL', provider=N'MSDASQL', provstr=N'DRIVER={MySQL ODBC 3.51 Driver}; SERVER=127.0.0.1; DATABASE=DB_Name; USER=UserName; PASSWORD=Password; OPTION=3' ?

RandomUs1r Over a year ago

@Mike here's sample code on php + ms sql: webcheatsheet.com/PHP/connect_mssql_database.php . From there you load up the data into php and dump it into mysql

|

Collectives™ on Stack Overflow

If there a way I can inner join a MS Sql table to a MySql Table in one query using MySql?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related