Migrate Code from MS SQL to MySQL

Modern companies and organizations are constantly looking for solutions to reduce operational expenses. One of the solutions following this way is to transfer IT infrastructure from expensive proprietary platforms to open-source equivalents that are supplied with loyal licenses such as MIT or GPL. In the world of DBMS this trend leads to shifting from such systems as SQL Server to MySQL and its variations. Database migration from SQL Server to MySQL is relatively simple task and also there are many tools to assist in this process. On another hand, migration of SQL code between these two database management systems may be quite complicated and can hardly be automated. This is why database specialists must be aware about basic rules of migrating source code from SQL Server to MySQL.

First, we compare main elements of SQL implemented by those two DBMS. SQL Server provides operator ‘+’ to concatenate strings. MySQL offers embedded function CONCAT(string, string2, …) for the same purpose. MS SQL has system variable @@ROWCOUNT exposing number of rows affected by the query being executed. MySQL offers embedded function FOUND_ROWS() for this purpose.

MS SQL supports schemas that are logic containers used to group and categorize object and to avoid naming conflicts. MySQL does not have such feature, so schema names should be either cut off or merged with table names. For example [schema].[table] in SQL Server becomes `schema.table` in MySQL.

Both MS SQL and MySQL support input and output parameters for stored procedures. However, SQL Server requires only output parameters to be specified using “OUTPUT” keyword. In MySQL both input and output parameters must be specified using “IN” and “OUT” keywords correspondingly.

Another topic is date/time arithmetic implemented in considered database management systems. SQL Server provides function GETDATE() to extract the current datetime and MySQL uses NOW() for the same purpose. MS SQL supports operator ‘+’ to add number of days to the specified date. MySQL uses function DATE_ADD($date, INTERVAL $n DAY) for the same purpose.

SQL Server queries may contain ‘TOP N’ pattern to filter first N rows of the result. In MySQL the same capabilities can be obtained using ‘LIMIT N’ clause.

The rules exposed above indicate that conversion of SQL code requires a lot of efforts when implementing it manually. In order to simplify migrating stored procedures, functions and triggers Intelligent Converters, one of leading companies in the field of database migration, developed MSSQL-to-MySQL Code Converter. The program partially automates migration procedure by converting common SQL patterns between dialects of these database management systems.

Main features of MSSQL-to-MySQL Code Converter:

• Most popular built-in functions are converted from SQL Server to MySQL
• MS SQL types are mapped into MySQL equivalents in the most intelligent way
• Accurate handling of reserved words and identifiers
• Input can be either live MS SQL database or T-SQL script file
• Command line support allows to script and schedule code migration procedure

Visit official site of Intelligent Converters to learn more about the product.