Importing Data from SQL Server to MariaDB

You are viewing an old version of this article. View the current version here.

There are several ways to move data from SQL Server to MariaDB. There are also some caveats.

Moving Data Definition from SQL Server to MariaDB

To copy an SQL Server data structures to MariaDB, one has to:

  1. Generate a CSV file from SQL Server data.
  2. Modify the syntax so that it works in MariaDB.
  3. Run the file in MariaDB.

Variables That Affect DDL Statements

DDL statements are affected by some server system variables.

sql_mode determines the behavior of some SQL statements and expressions, including how strict error checking is, and some details regarding the syntax. Objects like stored procedures, stored functions triggers and views, are always executed with the sql_mode that was in effect during their creation. sql_mode='MSSQL' can be used to have MariaDB behaving as close to SQL Server as possible.

innodb_strict_mode enables the so-called InnoDB strict mode. Normally some errors in the CREATE TABLE options are ignored. When InnoDB strict mode is enabled, the creation of InnoDB tables will fail with an error when certain mistakes are made.

updatable_views_with_limit determines whether view updates can be made with an UPDATE or DELETE statement with a LIMIT clause if the view does not contain all primary or not null unique key columns from the underlying table.

Dumps and sys.sql_modules

SQL Server Management Studio allows to create a working SQL script to recreate a database - something that MariaDB users refer to as a dump. Several options allow to fine-tune the generated syntax. It could be necessary to adjust some of these options to make the output compatible with MariaDB. It is possible to export schema, data or both. One can create a single global file, or one file for each exported object. Normally, producing a single file is more practical.

Alternatively, the sp_helptext() procedure returns information about how to recreate a certain object. Similar information is also present in the sql_modules table (definition column), in the sys schema. Such information, however, is not a ready to use set of SQL statements.

Remember however that does not support schemas. An SQL Server schema is approximately a MariaDB database.

To execute a dump, we can pass the file to mysql, the MariaDB command-line client.

Provided that a dump file contains syntax that is valid with MariaDB, it can be executed in this way:

mysql --show-warnings < dump.sql

--show-warnings tells MariaDB to output any warnings produced by the statements contained in the dump. Without this option, warnings will not appear on screen. Warnings don't stop the dump execution.

Errors will appear on screen. Errors will stop the dump execution, unless the --force option (or just -f) is specified.

For other mysql options, see mysql Command-line Client Options.

Another way to achieve the same purpose is to start the mysql client in interactive mode first, and then run the source command. For example:

root@d5a54a082d1b:/# mysql -uroot -psecret
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.4.7-MariaDB-1:10.4.7+maria~bionic binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \W
Show warnings enabled.
MariaDB [(none)]> source dump.sql

In this case, to show warnings we used the \W command, where "w" is uppercase. To hide warnings (which is the default), we can use \w (lowercase).

For other mysql commands, see mysql Commands.

CSV Data

If the table structures are already in MariaDB, we need to only import table data. While this can still be done as explained above, it may be more practical to export CSV files from SQL Server and import them into MariaDB.

SQL Server Management Studio and several other Microsoft tools allow to export CSV files.

MariaDB allows to import CSV files with the LOAD DATA INFILE statements, which is essentially the MariaDB equivalent for BULK INSERT.

It can happen that we don't want to import the whole data, but some filtered or transformed version of it. In that case, we may prefer to use the CONNECT storage engine to access CSV files and query them. The results of a query can be inserted into a table using INSERT SELECT.

Moving Data from MariaDB to SQL Server

Exporting a CSV file



Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.