Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

20 tips on converting MySQL views to MS SQL

By Andrew Kuprianov,

Although there are many free tools and solutions to migrate MySQL data to MS SQL server, none of them is able to convert views. That's why everybody who wants to migrate complete database have to convert views manually. This article gives 20 tips how to modify MySQL views to make it compatible with Microsoft SQL Server. 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='%database name%';

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

  1. if the query contains 'ORDER BY...' clause, it is necessary to insert 'TOP 100 PERCENT' right after 'SELECT' keyword
  2. replace all occurrenced of 'now()' by 'getdate()'
  3. replace 'JOIN ... WHERE' by 'CROSS JOIN ... WHERE'
  4. replace 'isnull(%expression%)' by '%expression% is null'
  5. replace all occurrenced of 'from_unixtime(%expression%)' by 'DateAdd(ss, %expression%, '01-01-1970')
  6. cut off codepage conversions (like '_cp1252') if any, since it is pure MySQL syntax
  7. replace 'curdate()' by 'getdate()'
  8. replace 'timediff(%expr1%, %expr2%)' by 'CAST(%expr1% - %expr2% AS TIME)'
  9. replace '%table1% cross join %table2% on %condition%' by '%table1% cross join %table2% where %condition%'
  10. replace 'conact(%expr1%, %expr2%, ..., %exprN%)' by '%expr1% + %expr2% + ... + %exprN%'
  11. replace 'SELECT ... LIMIT %number_of_rows%' by 'SELECT TOP %number_of_rows% ...'
  12. replace 'RAND()' by 'newID()' - it works in MS SQL 2005 and higher
  13. replace 'DAY(%expression%)' or 'DAYOFMONTH(%expression%)' by 'DATENAME(d, %expression%)'
  14. replace 'DAYOFYEAR(%expression%)' by 'DATENAME(dy, %expression%)'
  15. replace 'DAYNAME(%expression%)' by 'DATENAME(dw, %expression%)'
  16. replace 'HOUR(%expression%)' by 'DATENAME(hh, %expression%)'
  17. MySQL '... like %template%' is equal to 'CONTAINS(..., 'template')' in MS SQL
  18. MySQL '%expression% - INTERVAL 1 DAY' is equal to MS SQL 'dateAdd(day, -1, %expression%)'
  19. MySQL '%expression% - INTERVAL 1 MONTH' is equal to MS SQL 'dateAdd(month, -1, %expression%)'
  20. MySQL '%expression% - INTERVAL 1 YEAR' is equal to MS SQL 'dateAdd(year, -1, %expression%)'

Also you should remember that unlike MySQL MS SQL 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 newances 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 the following tools:

  • MySQL-to-MSSQL by Intelligent Converters - is inexpensive database migartion tool that converts about 50% of views in my tests. The price is $49. More details can be found at http://www.convert-in.com/sql2mss.htm
  • DBConvert for MS SQL & MySQL by DMSoft Technologies - bi-directional database migration tool that allows to convert MS SQL to MySQL and vice versa. Non of my test views has been converted. The price is $99. More details can be found at http://dbconvert.com/convert-mssql-to-mysql-pro.php
  • SqlTran by Spectralcore - sophistecated database translator that converts data, stored procedured, triggers and views. I don't have information about quality of conversion but vendor promises 100% result. The price is $999. More details can be found at http://www.sqltran.com/
Total article views: 2990 | Views in the last 30 days: 7
 
Related Articles
FORUM

how to convert mysql database in mac os to mssql in windows

how to convert mysql database in mac os to mssql in windows

ARTICLE

10 tips on converting MS SQL queries to MySQL

The syntax of SQL queries in MS SQL and MySQL are similar but not identical. This article discovers ...

FORUM

Expression

Expression

FORUM

derived column --replace " with space expression error

derived column --replace " with space expression error

ARTICLE

Stairway to SQL Dialects Level 3: MySQL

As part of the LAMP stack, MySQL is incredibly important for providing a reliable and platform-agnos...

Tags
migration    
mysql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones