Microsoft SQL Server to MariaDB Migration

SQLines open source tools can help you transfer data, convert database schema (DDL), views, stored procedures, functions, triggers, queries and SQL scripts from Microsoft SQL Server to MariaDB.

Databases:

Migration Reference

SQL Language Elements

Converting SQL language elements:

SQL Server MariaDB
1 string + string2 + … String concatenation CONCAT(string, string2, …)
2 @@IDENTITY Returns the last-inserted identity value LAST_INSERT_ID()

Data Types

Numeric data types:

SQL Server MariaDB
1 BIGINT 64-bit integer BIGINT
2 DECIMAL(p,s) Fixed-point number DECIMAL(p,s)
3 DOUBLE PRECISION Double-precision floating-point number DOUBLE PRECISION
4 FLOAT(p) Floating-point number DOUBLE
5 INT, INTEGER 32-bit integer INT, INTEGER
6 MONEY 64-bit currency amount DECIMAL(15,4)
7 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
8 REAL Single-precision floating-point number REAL
9 SMALLINT 16-bit integer SMALLINT
10 SMALLMONEY 32-bit currency amount DECIMAL(6,4)
11 TINYINT 8-bit unsigned integer, 0 to 255 TINYINT UNSIGNED

Datetime data types:

SQL Server MariaDB
1 DATE Date (year, month and day) DATE
2 DATETIME Date and time with fraction (milliseconds) DATETIME(3)
3 DATETIME2(p) Date and time with fraction, 0 ⇐ p ⇐ 7 DATETIME(p) p ⇐ 6
4 DATETIMEOFFSET(p) Date and time with fraction and time zone , 0 ⇐ p ⇐ 7 DATETIME(p) p ⇐ 6
5 SMALLDATETIME Datetime (year, month, day, hour, minutes and 00 seconds ) DATETIME
6 TIME(p) Time (Hour, minute, second and fraction), 0 ⇐ p ⇐ 7 TIME(p) p ⇐ 6

Built-in SQL Functions

Converting functions from SQL Server to MariaDB:

SQL Server MariaDB
1 CONVERT(DATETIME, string, style) Convert string to datetime STR_TO_DATE(string, format)
CONVERT(DATETIME, string) CONVERT(string, DATETIME)
2 CONVERT(VARCHAR, datetime, style) Convert datetime to string DATE_FORMAT(datetime, format)
3 DATEADD(unit, value, exp) Add datetime interval TIMESTAMPADD(unit, value, exp)
4 GETDATE() Get the current date and time NOW()

CREATE PROCEDURE Statement

Converting Transact-SQL stored procedures:

SQL Server MariaDB
1 CREATE PROCEDURE name CREATE PROCEDURE name
2 @param datatype [= default] [OUTPUT] IN | OUT | INOUT p_param datatype no defaults
3 Optional () for procedure parameters () required
4 AS before the procedure body Removed
5 BEGIN END is optional for the procedure body BEGIN END required
6 Optional statement delimiter ; is added after each statement
7 GO Specified by DELIMITER command

For further information, see Conversion of Transact-SQL Statements.

Transact-SQL Statements

Converting Transact-SQL statements from SQL Server to MariaDB:

Variable declaration and assignment:

SQL Server MariaDB
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 TABLE CREATE TEMPORARY TABLE vtab
4 SET @var = expression SET var = expression
5 SET @var = (SELECT expression FROM …) SET var = (SELECT expression FROM …)

Flow-of-control statements:

SQL Server MariaDB
1 IF condition BEGIN … END IF statement IF condition THEN … END IF;
2 IF … ELSE IF … IF ELSE IF statement IF … ELSEIF …
3 WHILE condition BEGIN stmts END Conditional loop WHILE condition DO stmts END WHILE;

Transaction statements:

SQL Server MariaDB
1 BEGIN TRANSACTION | TRAN Begin a transaction START TRANSACTION
2 COMMIT TRANSACTION | TRAN Commit transaction COMMIT
3 ROLLBACK TRANSACTION | TRAN Rollback transaction ROLLBACK