SQLines tools can 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:
Converting SQL language elements and constructs:
SQL Server | MySQL | |||
1 | -- comment | Single line comment | -- comment | A blank is required after -- |
2 | '\' | Backslash in string literal | '\\' | Unless NO_BACKSLASH_ESCAPES is enabled |
3 | func (param, ...) | Blank after function name | func(param, ...) | Blank is not always allowed |
4 | string + string2 + ... | String concatenation | CONCAT(string, string2, ...) | |
5 | 0x1234567890ABCDEF | Hexadecimal number (literal) | 0x1234567890ABCDEF | |
6 | @@ROWCOUNT | Get the number of affected rows | FOUND_ROWS() |
Datetime arithmetics:
SQL Server | MySQL | |||
1 | GETDATE() + n | Add n days to a datetime | ADDDATE(NOW(), n) | |
DATE_ADD(NOW(), INTERVAL n DAY) | ||||
TIMESTAMPADD(DAY, n, NOW()) | ||||
2 | GETDATE() + 0.1 | Add 0.1 of 24 hours i.e. 144 minutes | DATE_ADD(NOW(), INTERVAL 144 MINUTE) |
Bitwise operators:
SQL Server | MySQL | |||
1 | exp1 ^ exp2 | Bitwise OR (XOR) | exp1 ^ exp2 |
Converting data types from SQL Server to MySQL:
Other data types:
SQL Server | MySQL | |||
1 | SYSNAME | System-defined type for identifiers | NVARCHAR(128) |
Data type synonyms:
SQL Server Data Type | SQL Server Synonym | MySQL Synonym |
CHAR | CHARACTER | CHARACTER |
DECIMAL | DEC | DEC |
Converting string functions:
SQL Server | MySQL | |||
1 | CHAR(ascii_code) | Convert ASCII code to character | CHAR(ascii_code USING ASCII) | |
2 | LEN(string) | Length in characters excluding trailing spaces | CHAR_LENGTH(RTRIM(string)) |
Converting datetime functions:
SQL Server | MySQL | ||
1 | CAST(string AS DATETIME) | Convert string to datetime | STR_TO_DATE(string, format) |
CAST(0x00.. AS DATETIME) | Convert hex value to datetime | CAST and interval expression | |
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 | FORMAT(datetime, format) | Convert to string with format | DATE_FORMAT(datetime, format) |
7 | GETDATE() | Get the current date and time | NOW(3) |
8 | GETUTCDATE() | Get the current UTC date and time | UTC_TIMESTAMP() |
9 | MONTH(datetime) | Extract month from datetime | MONTH(datetime) |
10 | YEAR(datetime) | Extract year from datetime | YEAR(datetime) |
Converting numeric functions:
SQL Server | MySQL | |||
1 | CONVERT(NUMERIC, exp) | Convert exp to number or truncate | CONVERT(exp, DECIMAL) | Param order, NUMERIC not supported |
CONVERT(DECIMAL, exp) |
Data type conversion functions:
SQL Server | MySQL | |||
1 | CAST(exp AS datatype) | Convert expression to another data type | CAST(exp AS datatype) |
NULL handling functions:
SQL Server | MySQL | |||
1 | ISNULL(exp, replacement) | Replace NULL with the specified value | IFNULL(exp, replacement) |
Converting JSON functions:
SQL Server | MySQL | ||
1 | JSON_QUERY(json, path) | Query JSON | JSON_EXTRACT(json, path) |
2 | OPENJSON(json, path) | Transform JSON to rows | JSON_TABLE(json, path COLUMNS ...) |
Converting other functions:
SQL Server | MySQL | ||
1 | HOST_NAME() | Get the host name | @@HOSTNAME |
2 | LEFT(string, n) | Return n leftmost characters from string | LEFT(string, n) |
3 | NEWID() | Generate UUID | UUID() |
4 | RAISERROR(text, sv, st) | Raise an error | SIGNAL statement |
5 | RIGHT(string, n) | Return n rightmost characters from string | RIGHT(string, n) |
6 | SCOPE_IDENTITY() | Get last identity value | LAST_INSERT_ID() |
7 | STR(float, len, decimal) | Convert float to string | CONVERT(float, CHAR) |
8 | STUFF(str, start, len, new) | Replace substring at start position with new substring | INSERT(str, start, len, new) |
9 | SUSER_NAME() | Get the user name | CURRENT_USER() |
Converting SQL queries:
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 … |
3 | SELECT ... INTO #tmp | Create a temporary table using SELECT | CREATE TEMPORARY TABLE tmp AS SELECT ... |
4 | CROSS APPLY | Correlated inner join | CROSS JOIN LATERAL |
5 | OUTER APPLY | Correlated outer join | LEFT OUTER JOIN LATERAL |
6 | STUFF((SELECT ... FOR XML PATH... | Aggregate concatenation | GROUP_CONCAT expression |
7 | SELECT ... FOR JSON PATH | Return result as JSON | SELECT JSON_ARRAYAGG(JSON_OBJECT(...)) |
8 | LOOP | HASH | MERGE | Join hints i.e. INNER LOOP JOIN | Removed |
Converting CREATE TABLE statement keywords and clauses:
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 Transact-SQL user-defined functions:
SQL Server | MySQL | ||
1 | CREATE FUNCTION | ALTER FUNCTION name | CREATE FUNCTION name | |
2 | @param [AS] datatype = default | p_param datatype | |
3 | () | Required for empty parameters | () |
4 | RETURNS datatype(len) | RETURNS datatype(len) | |
5 | RETURNS TABLE | RETURNS JSON array | |
6 | Optional AS before the function body | Removed | |
7 | Optional statement delimiter | ; is added after each statement |
For more information, see Conversion of Transact-SQL Statements.
Converting Transact-SQL stored procedures from SQL Server to MySQL:
For more information, see Conversion of Transact-SQL Statements.
Converting Transact-SQL triggers:
Trigger system tables:
SQL Server | MySQL | |||
1 | inserted | All inserted or updated rows | NEW.col1, NEW.col2, ... | Direct column reference |
For more information, see Conversion of Transact-SQL Statements.
Modifying a table:
SQL Server | MySQL | ||
1 | ALTER TABLE table ADD DEFAULT exp FOR column | Add column default | Moved to CREATE TABLE |
Modifying a trigger:
SQL Server | MySQL | ||
1 | ALTER TRIGGER name trigger_defintion | DROP TRIGGER name; CREATE TRIGGER name trigger_defintion |
For more information, see CREATE TRIGGER statement.
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; |
Exception block:
SQL Server | MySQL | ||
1 | BEGIN TRY … END TRY BEGIN CATCH … END CATCH | Exception block | DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN … END |
Execute SQL:
SQL Server | MySQL | ||
1 | EXEC (@var) | Execute dynamic SQL | SET @session_var = var; PREPARE stmt FROM @session_var; EXECUTE stmt; DEALLOCATE PREPARE stmt; |
Other Transact-SQL statements:
SQL Server | MySQL | |||
1 | PRINT 'text' | Send message to the client | Commented inside a procedural block | |
SELECT 'text' AS '' | Expression in SQL script | |||
\! echo 'text'; | String literal in SQL script |
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' |
2 | sp_addextendedproperty 'MS_DiagramPane1', … | Removed | |
3 | sp_addextendedproperty 'MS_DiagramPaneCount', … | Removed |