将 MySQL 数据库导入 MS SQL Server
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2621682/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import MySQL database into a MS SQL Server
提问by marionmaiden
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 数据库？
采纳答案by marionmaiden
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
http://www.codeproject.com/KB/database/migrate-mysql-to-mssql.aspx
回答by Zar Shardan
Use SQL Server Migration Assistant (SSMA)
In addition to MySQL it supports Oracle, Sybase and MS Access.
除了 MySQL 之外，它还支持 Oracle、Sybase 和 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.
它似乎非常聪明，甚至能够处理非平凡的传输。它还有一些命令行界面（除了 GUI），所以理论上它可以集成到一些批处理加载过程中。
This the current download link for MySQL version https://www.microsoft.com/en-us/download/details.aspx?id=54257
这是 MySQL 版本的当前下载链接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.
当前（2016 年 6 月）稳定版 6.0.1 在传输数据时与当前 (5.3.6) MySQL ODBC 驱动程序崩溃。一切都是 64 位。带有 5.1.13 ODBC 驱动程序的 5.3 版本工作正常。
回答by deklin
I suggest you to use mysqldumplike so:
我建议你像这样使用mysqldump：
mysqldump --compatible=mssql
mysqldump --compatible=mssql
phpMyAdminis still a web-app and could potentially have some limitations for large databases(script execution time, allocatable memory and so on).
phpMyAdmin仍然是一个网络应用程序，可能对大型数据库有一些限制（脚本执行时间、可分配内存等）。
回答by Whakkee
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.
如果您使用PhpMyAdmin进行导出，则可以将 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 为我做这件事。
回答by Vladislav
Here is my approach for importing .sql files to MS SQL:
这是我将 .sql 文件导入 MS SQL 的方法：
Export table from MySQL with
--compatible=mssqland--extended-insert=FALSEoptions:mysqldump -u [username] -p --compatible=mssql --extended-insert=FALSE db_name table_name > table_backup.sqlSplit the exported file with PowerShell by 300000 lines per file:
$i=0; Get-Content exported.sql -ReadCount 300000 | %{$i++; $_ | Out-File out_$i.sql}Run each file in MS SQL Server Management Studio
使用
--compatible=mssql和--extended-insert=FALSE选项从 MySQL 导出表：mysqldump -u [username] -p --compatible=mssql --extended-insert=FALSE db_name table_name > table_backup.sql使用 PowerShell 将导出的文件按每个文件 300000 行拆分：
$i=0; Get-Content exported.sql -ReadCount 300000 | %{$i++; $_ | Out-File out_$i.sql}在 MS SQL Server Management Studio 中运行每个文件
There are few tips how to speed up the inserts.
有几个技巧可以加快插入速度。
Other approach is to use mysqldump –whereoption. By using this option you can split your table on any condition which is supported by wheresql clause.
其他方法是使用 mysqldump–where选项。通过使用此选项，您可以在wheresql 子句支持的任何条件下拆分表。
回答by Vladimir Korobkov
I had a very similar issue today - I needed to copy a big table(5 millions rows) from MySql into MS SQL.
我今天遇到了一个非常相似的问题 - 我需要将一个大表（5 百万行）从 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.
Installed MS SQL command line: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools#ubuntu
Dumped table from MySql to a file:
在 MS SQL 中创建了一个表，其结构与 MySql 中的源表相匹配。
已安装的 MS SQL 命令行：https: //docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-tools#ubuntu
将表从 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-Finally I iterated over these small files, did some text replacements, and executed the pieces one by one against MS SQL server:
在我的情况下，转储文件非常大，所以我决定将它分成许多小块（每个 1000 行）-
split --lines=1000 "$FILENAME" part-最后，我遍历了这些小文件，进行了一些文本替换，并针对 MS SQL 服务器逐个执行了这些片段：
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用你的，等等。
Hope that helps.
希望有帮助。
回答by Pinguin Pinguin
For me it worked best to export all data with this command:
对我来说，最好使用以下命令导出所有数据：
mysqldump -u USERNAME -p --all-databases --complete-insert --extended-insert=FALSE --compatible=mssql > backup.sql
--extended-insert=FALSE is needed to avoid mssql 1000 rows import limit.
--extended-insert=FALSE 需要避免 mssql 1000 行导入限制。
I created my tables with my migration tool, so I'm not sure if the CREATE from the backup.sql file will work.
我用我的迁移工具创建了我的表，所以我不确定来自 backup.sql 文件的 CREATE 是否可以工作。
In MSSQL's SSMS I had to imported the data table by table with the IDENTITY_INSERT ON to write the ID fields:
在 MSSQL 的 SSMS 中，我必须使用 IDENTITY_INSERT ON 逐表导入数据以写入 ID 字段：
SET IDENTITY_INSERT dbo.app_warehouse ON;
GO
INSERT INTO "app_warehouse" ("id", "Name", "Standort", "Laenge", "Breite", "Notiz") VALUES (1,'01','Bremen',250,120,'');
SET IDENTITY_INSERT dbo.app_warehouse OFF;
GO
If you have relationships you have to import the child first and than the table with the foreign key.
如果您有关系，则必须先导入子项，然后再导入带有外键的表。
回答by Francesco Mantovani
Run:
跑：
mysqldump -u root -p your_target_DB --compatible=mssql > MSSQL_Compatible_Data.sql
Do you want to see a process bar?
您想查看进程栏吗？
pv mysqldump -u root -p your_target_DB --compatible=mssql > MSSQL_Compatible_Data.sql
回答by Amir Keshavarz
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/
