Differences

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

sql-server-to-mariadb [June 18, 2018 2:20 pm]
sqlines
sql-server-to-mariadb [May 26, 2021 4:44 am] (current)
sqlines
Line 1: Line 1:
-====== Microsoft SQL Server to MariaDB Migration ======+====== Microsoft SQL Server (MS SQL) 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.+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 MariaDB.
-  * [[/sqldata/sql-server-to-mariadb|SQLines Data]] - Schema, data migration and validation tool +  * [[/sqldata/sql-server-to-mariadb|SQLines Data]] - Data transfer, schema migration and validation tool 
-  * SQLines SQL Converter - SQL scripts conversion tool+  * SQLines SQL Converter - SQL scripts assessment and conversion tool
Databases: Databases:
-  * Microsoft SQL Server 2016, 2014, 2012, 2008, 2005 and 2000 +  * Microsoft SQL Server 2019, 2017, 2016, 2014, 2012, 2008, 2005 and 2000 
-  * MariaDB 10.x and 5.x+  * MariaDB 10.x
Line 22: Line 22:
  * [[/sql-server-to-mariadb#data-types|Data Types]]   * [[/sql-server-to-mariadb#data-types|Data Types]]
  * [[/sql-server-to-mariadb#built-in-sql-functions|Built-in SQL Functions]]   * [[/sql-server-to-mariadb#built-in-sql-functions|Built-in SQL Functions]]
 +  * [[/sql-server-to-mariadb#alter-table-statement|ALTER TABLE]]
  * [[/sql-server-to-mariadb#create-procedure-statement|CREATE PROCEDURE]]   * [[/sql-server-to-mariadb#create-procedure-statement|CREATE PROCEDURE]]
  * [[/sql-server-to-mariadb#transact-sql-statements|Transact-SQL Statements]]   * [[/sql-server-to-mariadb#transact-sql-statements|Transact-SQL Statements]]
 +  * [[/sql-server-to-mariadb#system-procedures|System Procedures]]
===== SQL Language Elements ===== ===== SQL Language Elements =====
Line 55: Line 57:
| 1 | DATE | Date (year, month and day) | DATE || | 1 | DATE | Date (year, month and day) | DATE ||
| 2 | DATETIME | Date and time with fraction (milliseconds) | DATETIME(3) || | 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  {{:exclamation.png|}} | +| 3 | DATETIME2(//p//) | Date and time with fraction, 0 %%<=%% //p// %%<=%% 7 | DATETIME(//p//) | //p// %%<=%% 6  {{:exclamation.png|}} | 
-| 4 | DATETIMEOFFSET(//p//) | Date and time with fraction and time zone {{:exclamation.png|}}, 0 <= //p// <= 7 | DATETIME(//p//) | //p// <= 6  {{:exclamation.png|}} |+| 4 | DATETIMEOFFSET(//p//) | Date and time with fraction and time zone {{:exclamation.png|}}, 0 %%<=%% //p// %%<= 7%% | DATETIME(//p//) | //p// %%<=%% 6  {{:exclamation.png|}} |
| 5 | SMALLDATETIME | Datetime (year, month, day, hour, minutes and 00 seconds {{:exclamation.png|}}) | DATETIME || | 5 | SMALLDATETIME | Datetime (year, month, day, hour, minutes and 00 seconds {{:exclamation.png|}}) | DATETIME ||
-| 6 | TIME(//p//) | Time (Hour, minute, second and fraction), 0 <= //p// <= 7 | TIME(//p//) | //p// <= 6  {{:exclamation.png|}} |+| 6 | TIME(//p//) | Time (Hour, minute, second and fraction), 0 %%<=%% //p// %%<=%% 7 | TIME(//p//) | //p// %%<=%% 6  {{:exclamation.png|}} |
===== Built-in SQL Functions ===== ===== Built-in SQL Functions =====
Line 70: Line 72:
| 3 | DATEADD(//unit//, //value//, //exp//) | Add datetime interval | TIMESTAMPADD(//unit//, //value//, //exp//) | | 3 | DATEADD(//unit//, //value//, //exp//) | Add datetime interval | TIMESTAMPADD(//unit//, //value//, //exp//) |
| 4 | GETDATE() | Get the current date and time | NOW() | | 4 | GETDATE() | Get the current date and time | NOW() |
 +
 +===== ALTER TABLE Statement =====
 +
 +Modifying a table:
 +
 +| | **SQL Server** || **MariaDB** |
 +| 1 | ALTER TABLE //table// ADD DEFAULT //exp// FOR //column// | Add column default | Moved to CREATE TABLE {{:exclamation.png|}} |
 +
===== CREATE PROCEDURE Statement ===== ===== CREATE PROCEDURE Statement =====
Line 113: Line 123:
| 2 | COMMIT TRANSACTION %%|%% TRAN | Commit transaction | COMMIT | | 2 | COMMIT TRANSACTION %%|%% TRAN | Commit transaction | COMMIT |
| 3 | ROLLBACK TRANSACTION %%|%% TRAN | Rollback transaction | ROLLBACK | | 3 | ROLLBACK TRANSACTION %%|%% TRAN | Rollback transaction | ROLLBACK |
 +
 +===== System Procedures =====
 +
 +Converting system procedure calls from SQL Server to MariaDB:
 +
 +| | **SQL Server** || **MariaDB** |
 +| 1 | sp_addextendedproperty 'MS_Description', \\ //'Table comment'//,  ... 'table', //'table_name'// | Comment on table | ALTER TABLE //table_name// COMMENT //'Table comment'//  |