DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • Connecting Red Hat Single Sign-on on Openshift to an External Database
  • Reference Architecture: Deploying WSO2 API Manager on Microsoft Azure
  • Database synchronisation techniques - The Good, the Bad, and the Ugly
  • An In-Depth Look at Oracle MySQL HeatWave

Trending

  • Spring Authentication With MetaMask
  • Writing Reusable SQL Queries for Your Application With DbVisualizer Scripts
  • How to Migrate Vector Data from PostgreSQL to MyScale
  • An Introduction to Build Servers and Continuous Integration
  1. DZone
  2. Data Engineering
  3. Databases
  4. Converting MySQL Queries to MS SQL Server Queries

Converting MySQL Queries to MS SQL Server Queries

Brian Swan user avatar by
Brian Swan
·
Dec. 07, 10 · Interview
Like (0)
Save
Tweet
Share
27.28K Views

Join the DZone community and get the full member experience.

Join For Free

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.

Database MySQL Microsoft SQL Server

Opinions expressed by DZone contributors are their own.

Related

  • Connecting Red Hat Single Sign-on on Openshift to an External Database
  • Reference Architecture: Deploying WSO2 API Manager on Microsoft Azure
  • Database synchronisation techniques - The Good, the Bad, and the Ugly
  • An In-Depth Look at Oracle MySQL HeatWave

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: