Converting MySQL Queries to MS SQL Server Queries

At the JumpIn! Camp that took place a few weeks ago, PHP developers from several countries were invited to come to Redmond and work closely with Microsoft developers to build support for SQL Server and SQL Azure into their applications.  (As expected, the camp was more than just that. It was also an excellent two-way learning experience…you can read more about the camp here). One of the challenges that these PHP developers faced was in migrating their MySQL schemas and data to SQL Server. The tool that helped in doing this was the SQL Server Migration Assistant for MySQL 1.0 (SSMA), which, by most accounts, did a good job at converting databases. (For a quick tutorial on how to use this tool to migrate a database, see this post.) However, after converting databases, developers still had to convert individual queries from MySQL syntax to SQL Server syntax in order to build SQL Server support into their applications. For most simple queries, this isn’t terribly difficult. However, for some queries, the conversion is not at all straight forward. What I didn’t know (and was happy to learn) was that SSMA can also help convert queries. So, in this post I’ll show you how to use SSMA to convert a single MySQL query to a SQL Server query.

1. Create a Project: All the information you need for downloading SSMA, creating a project and connecting to databases is in steps 1-6 of this blog post. In the remainder of this post (as in the linked-to post), I’ll use the WordPress database as my example database.

2. In the MySQL Metadata Explorer, navigate to the Statements directory of your MySQL database:

3. Paste the query you want to convert into the query editor window. This is the query I’ll convert:

SELECT post_title, post_date FROM wp_posts ORDER BY post_date LIMIT 5 OFFSET 5;

4. Back in the MySQL metadata explorer, right-click Statements and select Convert Schema:

5.When prompted to save changes, select Yes:

6. Copy the converted query from the SQL Server query editor window:

That’s it…repeat for other queries as necessary. Note that SSMA will not successfully translate all MySQL queries, but it does for most. It does not translate some MySQL-specific functions (for example FOUND_ROW()). However, I have found that if it doesn’t translate a query successfully, it often provides enough of a clue that I can figure out ways to manually write the translation. Try it out and let me know what you think.