Home > Documentation > MySQL to SQL Server

Convert MySQL Views to SQL Server

One of the important steps of MySQL to SQL Server database migration is queries conversion. This article gives some tips on how to modify MySQL views to make it compatible with SQL Server format. The target audience for this guide should have general database management knowledge and experience in composing SQL queries.

First, you need to get list of all views in database as follows:

SELECT table_name, view_definition
 FROM information_schema.views
   WHERE table_schema='0atabase name%';

And then start to modify each SQL Server view query according to the following rules:

for quoted identifiers replace all occurrenced of ` (backtick) by []
MySQL $expression LIKE $template is equal to CONTAINS($expression, template) in MS SQL
MySQL $date - INTERVAL 1 DAY is equal to MS SQL 'DateAdd(day, -1, $date)
MySQL $date - INTERVAL 1 MONTH is equal to MS SQL 'DateAdd(month, -1, $date)
MySQL $date - INTERVAL 1 YEAR is equal to MS SQL 'DateAdd(year, -1, $date)
all MySQL built-in functions missing in the target database have to be replaced by the appropriate SQL Server equivalents as it is described in this article

Also you should remember that unlike MySQL SQL Server requires all selected columns in SELECT ... GROUP BY ... queries to be either under GROUP BY clause or in aggregation functions.

Of course, there are a lot of nuances staying outside of this article, it just covers the most frequent issues in migrating views from MySQL to MS SQL. If you need a solution for complete migration of MySQL database to MS SQL server, take a look at MySQL to MS SQL converter.

Have questions? Contact us