Import MySQL database into a MS SQL Server

Question

I have a .sql file from a MySQL dump containing tables, definitions and data to be inserted in these tables. How can I convert this database represented in the dump file to a MS SQL Server database?

Solution 1

I found a way for this on the net

It demands a little bit of work, because it has to be done table by table. But anyway, I could copy the tables, data and constraints into a SQL Server database.

Here is the link

http://www.codeproject.com/KB/database/migrate-mysql-to-mssql.aspx

Solution 2

Use SQL Server Migration Assistant (SSMA)

In addition to MySQL it supports Oracle, Sybase and MS Access.

It appears to be quite smart and capable of handling even nontrivial transfers. It also got some command line interface (in addition to GUI) so theoretically it can be integrated into some batch load process.

This the current download link for MySQL version https://www.microsoft.com/en-us/download/details.aspx?id=54257

The current (June 2016) stable version 6.0.1 crashes with the current (5.3.6) MySQL ODBC driver while transferring data. Everything 64 bit. The 5.3 version with the 5.1.13 ODBC driver works fine.

Solution 3

I suggest you to use mysqldump like so:

mysqldump --compatible=mssql

phpMyAdmin is still a web-app and could potentially have some limitations for large databases (script execution time, allocatable memory and so on).

Solution 4

Here is my approach for importing .sql files to SQL Server:

1. Export table from MySQL with --compatible=mssql and --extended-insert=FALSE options:

   mysqldump -u [username] -p --compatible=mssql --extended-insert=FALSE db_name table_name > table_backup.sql

2. Split the exported file with PowerShell by 300000 lines per file:

   $i=0; Get-Content exported.sql -ReadCount 300000 | %{$i++; $_ | Out-File out_$i.sql}

3. Run each file in SQL Server Management Studio

There are few tips how to speed up the inserts.

Other approach is to use mysqldump –where option. By using this option you can split your table on any condition which is supported by where SQL clause.

Solution 5

If you do an export with PhpMyAdmin, you can switch sql compatibility mode to 'MSSQL'. That way you just run the exported script against your MS SQL database and you're done.

If you cannot or don't want to use PhpMyAdmin, there's also a compatibility option in mysqldump, but personally I'd rather have PhpMyAdmin do it for me.

View more solutions

Comments

[Jeroen]

Alas, this gives me all sorts of compatabilty problems. The script will contain backticks around column names, data types that don't exist in MSSQL, will violate the max-1000-inserts constraint, auto_increment keywords, etc. etc. This answer is a logical suggestion, but I'm afraid it doesn't work too well (at least: it didn't for me).

[Jake Munson]

This tool only works if you can install an ODBC connector from your PC to your MySQL DB. If you just have a .sql dump file you cannot use SSMA.

[Zar Shardan]

MySQL engine is free software, and there is a version for windows too. Installing it and loading the dump is a trivial job. If you are hoping to find a tool (even a commercial, let alone free) that will reliably convert an arbitrary MySQL script to its SQL Server equivalent - well, good luck with that.

[Jake Munson]

Good point. And that is what I ended up doing yesterday. Short of writing your own custom conversion utility, installing MySQL seems to be the best option. SSMA was a pain in the butt to use, but I eventually got it to work.

[Zar Shardan]

Agree, it's not the most user friendly piece of software, but gets the job done.

[DonBecker]

SSMA is a great option, this should be the accepted answer. I didn't find it hard to use at all.

[Vinicius Rocha]

It requires the SQL Server Agent that does not comes with SQL Server Express.

[Rosdi Kasim]

@Vinicius, I just migrated my data from MySQL 5.6 to SQL Server Express 2008 R2. What is the problem you are having?

[muttley91]

This is an old thread, but exactly how might I go about doing this with a locally hosted MySQL database (using WAMP)?

[Aquarion]

@Vinicius: It's not enabled by default, but if you start it in Administrator Tools > Services, it works with Express fine.

[Aquarion]

@rar: Same thing. The SSMA will connect to any MySQL server. You'll need to install the ODBC connectors for windows from mysql.com. Pay attention to version numbers, the SSMA only supports a limited range.

[Espanta]

That is when both Mysql and SQL are in one machine or they are connected through a network. This is not useful when two different machine exist and you have a Dump file from mysql. right?

[Kiel]

This answer worked for me, with minimal modifications to the generated script. Brought it into SQL Server 2012. Didn't see the compatibility mode before.

[MonsterMMORPG]

how can we export entire database instead of table by table

[Vladislav]

try --databases [db_name] keyword as it explained in this answer: stackoverflow.com/a/26096339/155687

[Jason]

This worked for me using MS SQL 2014 and the 7.6.0 version of the Migration Assistant and the 5.3 MySQL ODBC connector. It's a kludgy interface but it does the job.

[Shadi Alnamrouti]

The links are obsolete. Use this: microsoft.com/en-us/download/details.aspx?id=54255

[Zar Shardan]

@ShadiNamrouti Thanks, updated the links. Yours was for Access though.

[Thomas Taylor]

mysqldump --compatible=ansi "Produce output that is more compatible with other database systems or with older MySQL servers. The only permitted value for this option is ansi, which has the same meaning as the corresponding option for setting the server SQL mode."

[Cribber]

Welcome to Stackoverflow @gorgino! If you post a link, please also post a short summary of the content in case the link will be down in the future. Linking should mainly be used to expand your answer, not be your answer.

[Vetsin]

dev.mysql.com/doc/refman/8.0/en/… - this does not do what you think it does