Using MySQL Linked Servers with OPENQUERY

Development

It is possible to query a MySQL database as a linked server from Microsoft SQL Management Studio (SSMS), indirectly, using OPENQUERY. 

Querying a Linked Server

To select all records from a database table in the linked server:

SELECT * FROM OPENQUERY(LINKED_MYSQL_DB, 'SELECT * FROM databasename.databasetable')

Or to find a record with a field matching some condition:

SELECT * FROM OPENQUERY(LINKED_MYSQL_DB, 'SELECT * FROM databaseame.users') WHERE name LIKE '%PersonName%';

There’s one problem with the above queries: They will read the entire table through the linked servers before the rows are filtered. Add the filter within the OPENQUERY instruction for larger tables:

SELECT * FROM OPENQUERY(LINKED_MYSQL_DB, 'SELECT * FROM databasename.users WHERE name LIKE "%s"');

Inserting a Record Into a Linked Server

The syntax is a bit strange here, 

insert openquery(LINKED_MYSQL_DB,
'select name, email, username, created_at, updated_at, department_id from databasename.users ')
Select 'James Sequel', 'james.sequel@organisation.net', 'jsequel', '2023-05-05 11:01:36', '2023-05-05 11:01:36', '20'