Differences

This shows you the differences between two versions of the page.

--- mysql-to-sql-server [April 24, 2017 8:19 pm]
sqlines
+++ mysql-to-sql-server [June 23, 2024 10:13 pm] (current)
sqlines
@@ Line 1: / Line 1: @@
 ====== MySQL to Microsoft SQL Server Migration ======
-SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures and functions, triggers, queries, embedded SQL statements and SQL scripts from MySQL to Microsoft SQL Server.
+SQLines tools can help you transfer data, convert database schema (DDL), views, stored procedures and functions, triggers, SQL queries and SQL scripts from MySQL to Microsoft SQL Server (MS SQL, MSSQL), Azure SQL and Azure Synapse.
-We also help convert embedded SQL statements in C/C++ (ODBC, MySQL C API), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET and Perl/PHP applications.
+  * [[/overview|SQLines SQL Converter]] - SQL Conversion and Assessment tool
+  * [[/sqldata/mysql-to-sql-server|SQLines Data]]  - Data Transfer, Schema Migration and Validation tool
-  * **MySQL** 5.x and 4.x
+**Databases**
-  * **Microsoft SQL Server** 2014, 2012, 2008 and 2005
-===== MySQL to SQL Server Migration Tools =====
+  * MySQL 8.x and 5.x
+  * Microsoft SQL Server 2022, 2019, 2017, 2016, 2014, 2012, 2008 and 2005
-SQLines tools to help you migrate from MySQL to SQL Server:
-  * [[/mysql-to-sql-server#sqlines-sql-converter-tool|SQLines SQL Converter - SQL Scripts Conversion Tool]]
-  * [[/sqldata/mysql-to-sql-server|SQLines Data - Data Transfer and Database Schema Migration and Validation Tool]]
-  * [[http://www.sqlines.com/online|Online SQL Conversion Tool]]
-===== SQLines SQL Converter Tool =====
-SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from MySQL to SQL Server.
-SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from a MySQL database use [[/sqldata/mysql-to-sql-server|SQLines Data]] tool.
+===== Migration Reference =====
-===== MySQL to Microsoft SQL Server Migration Reference =====
-Technical information on migration from MySQL to Microsoft SQL Server.
+  * [[/mysql-to-sql-server#sql-language-elements|SQL Language Elements]]
   * [[/mysql-to-sql-server#identifiers|Identifiers]]
   * [[/mysql-to-sql-server#data-types|Data Types]]
   * [[/mysql-to-sql-server#built-in-sql-functions|Built-in SQL Functions]]
+  * [[/mysql-to-sql-server#create-table-statement|CREATE TABLE]]
+  * [[/mysql-to-sql-server#select-statement|SELECT]]
   * [[/mysql-to-sql-server#create-procedure-statement|CREATE PROCEDURE]]
+  * [[/mysql-to-sql-server#procedural-sql-statements|Procedural SQL Statements]]
-**Last Update:** MySQL 5.6 and Microsoft SQL Server 2012
+===== SQL Language Elements =====
+Converting language elements and constructs:
+| | **MySQL** || **SQL Server ** ||
+| 1 | b'100' | Binary string | 0x04 | Hex string only {{:exclamation.png|}} |
 ===== Identifiers =====
@@ Line 44: / Line 43: @@
 ===== Data Types =====
-Converting data types:
+Converting character data types:
+| | **MySQL** || **SQL Server** ||
+| 1 | CHAR(//n//), CHARACTER(//n//) | Fixed-length string, 1 <= //n// <= 255 | CHAR(//n//), CHARACTER(//n//) ||
+| 2 | CHARACTER VARYING(//n//) | Variable-length string, 1 <= //n// <= 65535 {{:exclamation.png|}} | CHARACTER VARYING(//n//) ||
+| 3 | LONGTEXT | Character large object, <= 4G | VARCHAR(max) ||
+| 4 | LONG, LONG VARCHAR | Character large object, <= 16M | VARCHAR(max) ||
+| 5 | MEDIUMTEXT | Character large object, <= 16M | VARCHAR(max) ||
+| 6 | NCHAR(//n//) | Fixed-length UTF-8 string, 1 <= //n// <= 255 | NCHAR(//n//) ||
+| 7 | NVARCHAR(//n//) | Varying-length UTF-8 string, 1 <= //n// <= 65535 {{:exclamation.png|}} | NVARCHAR(//n//) ||
+| 8 | TEXT | Character large object, <= 64K | VARCHAR(max) ||
+| 9 | TINYTEXT | Character data, <= 255 bytes | VARCHAR(255) ||
+| 10 | VARCHAR(//n//) | Variable-length string, 1 <= //n// <= 65535 {{:exclamation.png|}} | VARCHAR(//n//) ||
+Converting numeric data types:
 | | **MySQL** || **SQL Server** ||
 | 1 | BIGINT | 64-bit integer | BIGINT ||
-| 2 | BINARY(//n//) | Fixed-length byte string, 1 <= //n// <= 255 | BINARY(//n//) ||
+| 2 | DECIMAL(//p,s//) | Fixed-point number, //p// <= 65, default 10 | DECIMAL(//p,s//) | //p// <= 38, default 18 {{:exclamation.png|}} |
-| 3 | BIT(//n//) | Fixed-length bit string, 1 <= //n// <= 64 | BINARY(//n///8) ||
+| 3 | DEC(//p,s//) | Synonym for DECIMAL | DEC(//p,s//) ||
-| 4 | BLOB(//n//) | Binary large object, <= 64K | VARBINARY(max) ||
+| 4 | DOUBLE [PRECISION] | Double-precision floating-point number | FLOAT ||
-| 5 | BOOLEAN, BOOL | 0 or 1 value; NULL is not allowed | BIT ||
+| 5 | FIXED(//p,s//) | Synonym for DECIMAL | DECIMAL(//p,s//) ||
-| 6 | CHAR(//n//), CHARACTER(//n//) | Fixed-length string, 1 <= //n// <= 255 | CHAR(//n//), CHARACTER(//n//) ||
+| 6 | FLOAT(//p//) | Floating-point number | FLOAT ||
-| 7 | CHARACTER VARYING(//n//) | Variable-length string, 1 <= //n// <= 65535 {{:exclamation.png|}} | CHARACTER VARYING(//n//) ||
+| 7 | FLOAT4(//p//) | Floating-point number | FLOAT ||
-| 8 | DATE | Date (year, month and day) | DATE | Since SQL Server 2008 {{:exclamation.png|}} |
+| 8 | FLOAT8 | Double-precision floating-point number | BINARY_DOUBLE ||
-| 9 | DATETIME(//p//) | Date and time data with fraction | DATETIME2(//p//) ||
+| 9 | INT, INTEGER | 32-bit integer | INT, INTEGER ||
-| 10 | DECIMAL(//p,s//), DEC(//p,s//) | Fixed-point number | DECIMAL(//p,s//), DEC(//p,s//) ||
+| 10 | INT1 | 8-bit integer | SMALLINT ||
-| 11 | DOUBLE [PRECISION] | Double-precision floating-point number | FLOAT ||
+| 11 | INT2 | 16-bit integer | SMALLINT ||
-| 12 | FIXED(//p,s//) | Fixed-point number | DECIMAL(//p,s//) ||
+| 12 | INT3 | 24-bit integer | INT ||
-| 13 | FLOAT(//p//) | Floating-point number | FLOAT ||
+| 13 | INT4 | 32-bit integer | INT ||
-| 14 | FLOAT4(//p//) | Floating-point number | FLOAT ||
+| 14 | INT8 | 64-bit integer | BIGINT ||
-| 15 | FLOAT8 | Double-precision floating-point number | BINARY_DOUBLE ||
+| 15 | MEDIUMINT | 24-bit integer | INT ||
-| 16 | INT, INTEGER | 32-bit integer | INT, INTEGER ||
+| 16 | MIDDLEINT | 24-bit integer | INT ||
-| 17 | INT1 | 8-bit integer | SMALLINT ||
+| 17 | NUMERIC(//p,s//) | Synonym for DECIMAL | NUMERIC(//p,s//) ||
-| 18 | INT2 | 16-bit integer | SMALLINT ||
+| 18 | REAL | Double-precision floating-point number | DOUBLE PRECISION ||
-| 19 | INT3 | 24-bit integer | INT ||
+| 19 | SERIAL | 64-bit autoincrementing integer | NUMERIC(20) {{:exclamation.png|}} ||
-| 20 | INT4 | 32-bit integer | INT ||
+| 20 | SMALLINT | 16-bit integer | SMALLINT ||
-| 21 | INT8 | 64-bit integer | BIGINT ||
+| 21 | TINYINT | 8-bit integer | SMALLINT ||
-| 22 | LONGBLOB | Binary large object, <= 4G | VARBINARY(max) ||
-| 23 | LONGTEXT | Character large object, <= 4G | VARCHAR(max) ||
+Converting date and time data types:
-| 24 | LONG VARBINARY | Binary large object, <= 16M | VARBINARY(max) ||
-| 25 | LONG, LONG VARCHAR | Character large object, <= 16M | VARCHAR(max) ||
+| | **MySQL** || **SQL Server** ||
-| 26 | MEDIUMBLOB | Binary large object, <= 16M | VARBINARY(max) ||
+| 1 | DATE | Date (year, month and day) | DATE | Since SQL Server 2008 {{:exclamation.png|}} |
-| 27 | MEDIUMINT | 24-bit integer | INT ||
+| 2 | DATETIME(//p//) | Date and time data with fraction | DATETIME2(//p//) ||
-| 28 | MEDIUMTEXT | Character large object, <= 16M | VARCHAR(max) ||
+| 3 | TIME(//p//) | Time (Hour, minute, second and fraction) | TIME(//p//) ||
-| 29 | MIDDLEINT | 24-bit integer | INT ||
+| 4 | TIMESTAMP(//p//) | Auto-updated datetime | DATETIME2(//p//) ||
-| 30 | NCHAR(//n//) | Fixed-length UTF-8 string, 1 <= //n// <= 255 | NCHAR(//n//) ||
+| 5 | YEAR[(2 %%|%% 4)] | Year in 2-digit or 4-digit format | NUMERIC(4) ||
-| 31 | NVARCHAR(//n//) | Varying-length UTF-8 string, 1 <= //n// <= 65535 {{:exclamation.png|}} | NVARCHAR(//n//) ||
-| 32 | NUMERIC(//p,s//) | Fixed-point number | NUMERIC(//p,s//) ||
+Converting other data types:
-| 33 | REAL | Double-precision floating-point number | DOUBLE PRECISION ||
-| 34 | SERIAL | 64-bit autoincrementing integer | NUMERIC(20) {{:exclamation.png|}} ||
+| | **MySQL** || **SQL Server** ||
-| 35 | SMALLINT | 16-bit integer | SMALLINT ||
+| 1 | BINARY(//n//) | Fixed-length byte string, 1 <= //n// <= 255 | BINARY(//n//) ||
-| 36 | TEXT | Character large object, <= 64K | VARCHAR(max) ||
+| 2 | BIT(//n//) | Fixed-length bit string, 1 %%<=%% //n// %%<=%% 64 | BINARY(//n///8) ||
-| 37 | TIME(//p//) | Time (Hour, minute, second and fraction) | TIME(//p//) ||
+| 3 | BLOB(//n//) | Binary large object, <= 64K | VARBINARY(max) ||
-| 38 | TIMESTAMP(//p//) | Auto-updated datetime | DATETIME2(//p//) ||
+| 4 | BOOLEAN, BOOL | 0 or 1 value; NULL is not allowed | BIT ||
-| 39 | TINYBLOB | Binary data, <= 255 bytes | VARBINARY(255) ||
+| 5 | LONGBLOB | Binary large object, <= 4G | VARBINARY(max) ||
-| 40 | TINYINT | 8-bit integer | SMALLINT ||
+| 6 | LONG VARBINARY | Binary large object, <= 16M | VARBINARY(max) ||
-| 41 | TINYTEXT | Character data, <= 255 bytes | VARCHAR(255) ||
+| 7 | MEDIUMBLOB | Binary large object, <= 16M | VARBINARY(max) ||
-| 42 | VARBINARY(//n//) | Variable-length byte string, 1 <= //n// <= 65535 {{:exclamation.png|}} | VARBINARY(//n//) ||
+| 8 | TINYBLOB | Binary data, <= 255 bytes | VARBINARY(255) ||
-| 43 | VARCHAR(//n//) | Variable-length string, 1 <= //n// <= 65535 {{:exclamation.png|}} | VARCHAR(//n//) ||
+| 9 | VARBINARY(//n//) | Variable-length byte string, 1 <= //n// <= 65535 {{:exclamation.png|}} | VARBINARY(//n//) ||
-| 44 | YEAR[(2 %%|%% 4)] | Year in 2-digit or 4-digit format | NUMERIC(4) ||
 **Data Type Attributes**:
@@ Line 106: / Line 118: @@
 ===== Built-in SQL Functions =====
-Converting functions:
+Converting date and time functions:
 | | **MySQL** ||| **SQL Server** |
 | 1 | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP() | Get current date and time | GETDATE() |
+===== CREATE TABLE Statement =====
+Converting table definitions:
+| | **MySQL** || **SQL Server** ||
+| 1 | UNIQUE KEY %%|%% INDEX  //name// (//column, ...//) || CONSTRAINT //name// UNIQUE (//column, ...//) ||
+| 2 | KEY //name// (//column, ...//) | Inline non-unique index | CREATE INDEX //name// ON //table//(//column, ...//) |  Standalone statement {{:exclamation.png|}} |
+===== SELECT Statement =====
+Converting SQL queries:
+| | **MySQL** || **SQL Server** |
+| 1 | SELECT ... FROM ... LIMIT //n//   | Select //n// rows only | SELECT TOP //n// ... FROM ... |
 ===== CREATE PROCEDURE Statement =====
-Converting stored procedures from MySQL to SQL Server:
+Converting stored procedures:
 | | **MySQL** || **SQL Server** |
@@ Line 121: / Line 148: @@
 | 4 | No AS keyword before outer BEGIN END block || AS keyword required |
 | 5 | User-defined delimiter at the end || GO |
+For more information, see  [[/mysql-to-sql-server#procedural-sql-statements|Conversion of Procedural SQL Statements]].
+===== Procedural SQL Statements =====
+Procedural SQL statements used in stored procedures, functions and triggers:
+Flow-of-control statements:
+| | **MySQL** || **SQL Server** |
+| 1 | IF ... THEN ... ELSEIF ... ELSE ... END IF | IF statement | IF ... BEGIN ... END ELSE IF BEGIN ... END ELSE BEGIN ... END |

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications

Differences