SQLines provides tools to help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Microsoft SQL Server (MSSQL, MS SQL), Azure SQL Database, Azure Synapse to MySQL.
Databases:
Technical information on migration from Microsoft SQL Server to MySQL.
Converting SQL language elements from SQL Server to MySQL:
Datetime arithmetics:
SQL Server | MySQL | |||
1 | GETDATE() + n | Add n days to the current datetime | ADDDATE(NOW(), n) | |
DATE_ADD(NOW(), INTERVAL n DAY) | ||||
TIMESTAMPADD(DAY, n, NOW()) |
Converting data types from SQL Server to MySQL:
Data type synonyms:
SQL Server Data Type | SQL Server Synonym | MySQL Synonym |
CHAR | CHARACTER | CHARACTER |
DECIMAL | DEC | DEC |
Converting functions:
SQL Server | MySQL | ||
1 | CHAR(ascii_code) | Convert ASCII code to character | CAST(CHAR(ascii_code) AS CHAR) ![]() |
2 | CONVERT(DATETIME, string, style) | Convert string to datetime | STR_TO_DATE(string, format) |
3 | CONVERT(VARCHAR, datetime, style) | Convert datetime to string | DATE_FORMAT(datetime, format) |
4 | DATEADD(unit, value, exp) | Add datetime interval | TIMESTAMPADD(unit, value, exp) |
5 | DATEDIFF(units, start, end) | Get datetime difference in specified units | TIMESTAMPDIFF(units, start, end) |
6 | GETDATE() | Get the current date and time | NOW() |
7 | GETUTCDATE() | Get the current UTC date and time | UTC_TIMESTAMP() |
8 | HOST_NAME() | Get the host name | @@HOSTNAME |
9 | ISNULL(exp, replace) | Replace NULL | IFNULL(exp, replace) |
10 | MONTH(datetime) | Extract month from datetime | MONTH(datetime) |
11 | LEFT(string, n) | Return n leftmost characters from string | LEFT(string, n) |
12 | LEN(string) | Length in characters excluding trailing spaces ![]() | CHAR_LENGTH(RTRIM(string)) |
13 | NEWID() | Generate GUID | UUID() |
14 | RAISERROR(text, sv, st) | Raise an error | SIGNAL statement |
15 | RIGHT(string, n) | Return n rightmost characters from string | RIGHT(string, n) |
16 | SCOPE_IDENTITY() | Get last identity value | LAST_INSERT_ID() |
17 | STR(float, len, decimal) | Convert float to string | CONVERT(float, CHAR) |
18 | STUFF(str, start, len, new) | Replace substring at start position with new substring | INSERT(str, start, len, new) |
19 | SUSER_NAME() | Get the user name | CURRENT_USER() |
20 | YEAR(datetime) | Extract year from datetime | YEAR(datetime) |
Modifying a table:
SQL Server | MySQL | ||
1 | ALTER TABLE table ADD DEFAULT exp FOR column | Add column default | Moved to CREATE TABLE ![]() |
Converting CREATE TABLE statement keywords and clauses:
SQL Server | MySQL | |||
1 | IDENTITY(start, increment) | Identity column | AUTO_INCREMENT | Increment is always 1 ![]() |
2 | PRIMARY KEY CLUSTERED | Primary key Constraint | PRIMARY KEY |
Storage clause:
SQL Server | MySQL | |||
1 | ON PRIMARY | filegroup | File group to store the table | Removed | |
2 | TEXTIMAGE_ON PRIMARY | filegroup | File group to store large objects of the table | Removed |
Converting SQL queries from SQL Server to MySQL:
SQL Server | MySQL | ||
1 | SELECT TOP n … FROM … | Select n rows only | SELECT … FROM … LIMIT n |
2 | SELECT @v = c, @v2 = c2 FROM … | SELECT INTO statement | SELECT c, c2 INTO v, v2 FROM … |
Converting Transact-SQL stored procedures from SQL Server to MySQL:
For further information, see Conversion of Transact-SQL Statements.
Converting Transact-SQL user-defined functions from SQL Server to MySQL:
For further information, see Conversion of Transact-SQL Statements.
Converting SET statement for options from SQL Server to MySQL:
SQL Server | MySQL | ||
1 | SET ANSI_NULLS ON | OFF | Use = <> with NULLs | Commented |
2 | SET NOCOUNT ON | OFF | Send messages on affected rows | Removed |
3 | SET QUOTED_IDENTIFIER ON | OFF | Quote identifiers with "" | Commented |
Converting Transact-SQL statements from SQL Server to MySQL:
Variable declaration and assignment:
SQL Server | MySQL | |
1 | DECLARE @var [AS] type [= default_value] | DECLARE var type [DEFAULT default_value] |
2 | DECLARE @v1 type1, @v2 type2, … | DECLARE v1 type1; DECLARE v2 type2, DECLARE … |
3 | DECLARE @tab [AS] TABLE (…) | CREATE TEMPORARY TABLE tab (…) |
4 | SET @var = expression | SET var = expression |
5 | SET @var = (SELECT expression FROM …) | SET var = (SELECT expression FROM …) |
6 | SELECT @var = exp, @var2 = exp2 FROM … | SELECT exp, exp2 INTO var, var2 FROM … |
Cursor declaration and operations:
Flow-of-control statements:
SQL Server | MySQL | ||
1 | BREAK | Exit a loop | LEAVE label |
2 | IF condition BEGIN … END | IF statement | IF condition THEN … END IF; |
3 | IF … ELSE IF … | IF ELSE IF statement | IF … ELSEIF … |
4 | WHILE condition BEGIN stmts END | Conditional loop | WHILE condition DO stmts END WHILE; |
Other Transact-SQL statements:
Converting SQL statements from SQL Server to MySQL:
SQL Server | MySQL | ||
1 | USE dbname | Change the database | USE dbname |
Converting system procedure calls from SQL Server to MySQL:
SQL Server | MySQL | ||
1 | sp_addextendedproperty 'MS_Description', 'Table comment', … 'table', 'table_name' | Comment on table | ALTER TABLE table_name COMMENT 'Table comment' |