Over a million developers have joined DZone.

Converting MySQL Queries to MS SQL Server Queries

· Database Zone

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Couchbase.

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:

image

 

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;

image

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

image

5.When prompted to save changes, select Yes:

image

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

image

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.

Are you a developer? Don’t miss the monthly Couchbase Developer Community Newsletter, covering the latest headlines on NoSQL and much more. Brought to you in partnership with Couchbase.

Topics:
php ,sql ,microsoft ,mysql ,sql server

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}