20 tips to convert MySQL views to MS SQL

Posted on by Barbara Ratliff

There are a number of free tools and solutions to migrate MySQL data to MS SQL server but none of them is able to convert views. That’s why anyone who wants to migrate complete database has to convert the views manually. This article is about 20 tips about how to modify MySQL views to make it compatible with Microsoft SQL Server.

P.S. this guide requires general database management knowledge and experience in programming SQL for the target audience.

msql

List of Views

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

SELECT table_name, view_definition FROM information_schema.views WHERE table_schema=’abase name%’;

Tips

After getting the list, you can now officially start to modify each MS SQL view query following these rules.

  1. If MySQL query contains’ORDER BY…’ clause, it is necessary to insert ‘TOP 100 PERCENT’ right after ‘SELECT’ keyword in MS SQL
  1. Replace all occurrences of MySQL’now()’ by MS SQL’getdate()’
  1. Substitute MySQL’JOIN … WHERE’ by MS SQL’CROSS JOIN … WHERE’
  1. Replace MySQL’isnull(%expression%)’ by MS SQL’%expression% is null’
  1. Replace all occurrences of MySQL’from_unixtime(%expression%)’ by MS SQL’DateAdd(ss, %expression%, ’01-01-1970′)’
  1. Cut off code page conversions like ‘_cp1252’ (if any), since it is MySQL specific syntax that is not supported by Microsoft SQL
  1. Replace MySQL’curdate()’ by MS SQL’getdate()’
  1. Replace MySQL’timediff(%expr1%, %expr2%)’ by MS SQL’CAST(%expr1% – %expr2% AS TIME)’
  1. Replace MySQL patterns’table_1 cross join table_2 on some_condition’ by MS SQL equivalent ‘table_1 cross join table_2 where some_condition’
  1. Replace MySQL’concat(%expr1%, %expr2%, …, %exprN%)’ by MS SQL’%expr1% + %expr2% + … + %exprN%’
  1. Replace MySQL’SELECT … LIMIT number_of_rows’ by MS SQL’SELECT TOP number_of_rows …’
  1. Replace MySQL’RAND()’ by MS SQL’newID()’ (it works with MS SQL 2005 and higher)
  1. Replace MySQL’DAY(%expression%)’ or ‘DAYOFMONTH(%expression%)’ by MS SQL’DATENAME(d, %expression%)’
  1. Replace MySQL’DAYOFYEAR(%expression%)’ by MS SQL’DATENAME(dy, %expression%)’
  1. Substitute MySQL ‘DAYNAME(%expression%)’ by MS SQL’DATENAME(dw, %expression%)’
  1. Replace MySQL ‘HOUR(%expression%)’ by MS SQL’DATENAME(hh, %expression%)’
  1. MySQL expressions ‘… like %template%’ are equal to the function call MS SQL ‘CONTAINS(…, ‘template’)’
  1. MySQL fragments of query: ‘%expression% – INTERVAL 1 DAY’ are equal toMS SQL’dateAdd(day, -1, %expression%)’
  1. MySQL patterns: ‘%expression% – INTERVAL 1 MONTH’ are equal to MS SQL function call ‘dateAdd(month, -1, %expression%)’
  1. MySQL pattern ‘%some_date% – INTERVAL 1 YEAR’ has equivalent in Microsoft SQL: ‘dateAdd(year, -1, %expression%)’

One more tip is to remember that unlike MySQL, MS SQL requires all selected columns in ‘SELECT … GROUP BY …’ queries are located either inside ‘GROUP BY’ clause or in aggregation functions.

This article just covers the most frequent issues in migrating views from MySQL to Microsoft SQL Server. If you need a solution for complete migration of MySQL database to MS SQL server, have a look at MySQL to MSSQL converter developed by Intelligent Converters software company.