将MySQL数据库导入MS SQL服务器

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?

我有一个来自MySQL转储的.sql文件，其中包含要插入到这些表中的表、定义和数据。如何将转储文件中表示的数据库转换为MS SQL Server数据库?

8 个解决方案

#1

Use SQL Server Migration Assistant (SSMA)

使用SQL Server迁移助手(SSMA)

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

除了MySQL，它还支持Oracle、Sybase和MS访问。

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.

它似乎非常聪明，甚至能够处理重要的传输。它还具有一些命令行接口(除了GUI)，因此理论上它可以集成到一些批加载过程中。

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

这是MySQL版本http://www.microsoft.com/en-us/download/details.aspx?id=42657的当前下载链接

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.

当前(2016年6月)稳定版本6.0.1在传输数据时与当前(5.3.6)MySQL ODBC驱动程序崩溃。64位的一切。使用5.1.13 ODBC驱动程序的5.3版本运行良好。

#2

I suggest you to use mysqldump like so:

我建议您像这样使用mysqldump:

mysqldump --compatible=mssql

,mysqldump——=该软件兼容

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

phpMyAdmin仍然是一个web应用程序，对于大型数据库(脚本执行时间、分配内存等)可能有一些限制。

#3

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 MS SQL database.

它需要做一些工作，因为它需要一个表格一个表格地完成。但是无论如何，我可以将表、数据和约束复制到MS SQL数据库中。

Here is the link

这里是链接

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

#4

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

以下是我将.sql文件导入MS SQL的方法:

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

从MySQL导出表-compatible=mssql和- extension -insert=FALSE选项:

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

mysqldump -u [username] -p -compatible=mssql - extension -insert=FALSE db_name table_name > table_backup.sql
Split the exported file with PowerShell by 300000 lines per file:

用PowerShell将导出的文件分割为每个文件30万行:

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

$ i = 0;获取内容出口。sql -ReadCount 300000 |% {$i++;$ _ | Out-File out_ $ i.sql }
Run each file in MS SQL Server Management Studio

在MS 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.

另一种方法是使用mysqldump -where选项。通过使用此选项，您可以在where sql子句支持的任何条件下分割表。

#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.

如果您使用phmypadmin进行导出，您可以将sql兼容性模式切换到“MSSQL”。这样，您只需针对MS SQL数据库运行导出的脚本，就完成了。

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.

如果您不能或不想使用PhpMyAdmin, mysqldump中还有一个兼容性选项，但我个人宁愿让PhpMyAdmin替我做。

#6

I had a very similar issue today - I needed to copy a big table(5 millions rows) from MySql into MS SQL.

我今天遇到了一个非常类似的问题——我需要将一个大表(500万行)从MySql复制到MS SQL中。

Here are the steps I've done(under Ubuntu Linux):

以下是我在Ubuntu Linux下完成的步骤:

Created a table in MS SQL which structure matches the source table in MySql.

在MS SQL中创建一个表，该表的结构与MySql中的源表匹配。
Installed MS SQL command line: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools#ubuntu

安装MS SQL命令行:https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup工具#ubuntu
Dumped table from MySql to a file:

将表从MySql转储到文件:

mysqldump \
    --compact \
    --complete-insert \
    --no-create-info \
    --compatible=mssql \
    --extended-insert=FALSE \
    --host "$MYSQL_HOST" \
    --user "$MYSQL_USER" \
    -p"$MYSQL_PASS" \
    "$MYSQL_DB" \
    "$TABLE" > "$FILENAME"

In my case the dump file was quite large, so I decided to split it into a number of small pieces(1000 lines each) - split --lines=1000 "$FILENAME" part-

在我的例子中，转储文件相当大，所以我决定将它分割成许多小块(每个1000行)—split—lines=1000“$FILENAME”部分
Finally I iterated over these small files, did some text replacements, and executed the pieces one by one against MS SQL server:

最后，我遍历了这些小文件，做了一些文本替换，并逐个执行了对MS SQL server的操作:

export SQLCMD=/opt/mssql-tools/bin/sqlcmd

x=0

for file in part-*
do
  echo "Exporting file [$file] into MS SQL. $x thousand(s) processed"

  # replaces \' with ''
  sed -i "s/\\\'/''/g" "$file"

  # removes all "
  sed -i 's/"//g' "$file"

  # allows to insert records with specified PK(id)
  sed -i "1s/^/SET IDENTITY_INSERT $TABLE ON;\n/" "$file"

  "$SQLCMD" -S "$AZURE_SERVER" -d "$AZURE_DB" -U "$AZURE_USER" -P "$AZURE_PASS" -i "$file"
  echo ""
  echo ""

  x=$((x+1))
done

echo "Done"

Of course you'll need to replace my variables like $AZURE_SERVER, $TABLE , e.t.c. with yours.

当然，您需要替换我的变量，如$AZURE_SERVER、$TABLE、e.t.c.和您的。

Hope that helps.

希望有帮助。

#7

This guy has a simple trick using MS Access as a tunel between MySQL and MSSQL

这家伙有一个简单的技巧，使用MS Access作为MySQL和MSSQL之间的tunel

You can read the post at http://azamudd.in/convert-data-from-mysql-into-mssql-the-easy-way-via-ms-access/

您可以在http://azamudd.in/convertdata -from-mysql-into-mssql- easy-way-via-ms-access/上阅读本文

#8

Also you can use 'ODBC' + 'SQL Server Import and Export Wizard'. Below link describes it: https://www.mssqltips.com/sqlservertutorial/2205/mysql-to-sql-server-data-migration/

还可以使用“ODBC”+“SQL Server导入和导出向导”。下面的链接描述它:https://www.mssqltips.com/sqlservertutorial/2205/mysql-to-sql-server-data-migration/

#1