如何将数据从 SQLServer2005导出到 MySQL

我一直在用头撞 SQL Server 2005试图得到大量的数据。我得到了一个包含近300个表的数据库，我需要把它变成一个 MySQL 数据库。我的第一个调用是使用 bcp，但是不幸的是它不能产生有效的 CSV ——字符串没有被封装，所以你不能处理任何有逗号的字符串的行(或者你用作分隔符的任何东西) ，我仍然必须手写所有的 create 表语句，因为显然 CSV 没有告诉你任何关于数据类型的信息。

如果有一些工具可以同时连接到 SQLServer 和 MySQL，那就更好了。您会丢失视图、存储过程、触发器等，但是将一个只使用基类型的表从一个数据库复制到另一个数据库并不困难... ... 不是吗？

有人知道这种工具吗？只要它支持整数、浮点数、日期时间和字符串，我不介意它做出多少假设或进行哪些简化。我必须做很多修剪，正常化等，所以我不关心保持键，关系或任何类似的东西，但我需要在快速的初始数据集！

96519 次浏览
小开

Rolling your own PHP solution will certainly work though I'm not sure if there is a good way to automatically duplicate the schema from one DB to the other (maybe this was your question).

If you are just copying data, and/or you need custom code anyway to convert between modified schemas between the two DB's, I would recommend using PHP 5.2+ and the PDO libraries. You'll be able to connect using PDO ODBC (and use MSSQL drivers). I had a lot of problems getting large text fields and multi-byte characters from MSSQL into PHP using other libraries.

小开

Another tool to try would be the SQLMaestro suite. It is a little tricky nailing down the precise tool, but they have a variety of tools, both free and for purchase that handle a wide variety of tasks for multiple database platforms. I'd suggest trying the Data Wizard tool first for MySQL, since I believe that will have the proper "import" tool you need.

小开

The best way that I have found is the MySQL Migration Toolkit provided by MySQL. I have used it successfully for some large migration projects.

小开

SQL Server 2005 "Standard", "Developer" and "Enterprise" editions have SSIS, which replaced DTS from SQL server 2000. SSIS has a built-in connection to its own DB, and you can find a connection that someone else has written for MySQL. Here is one example. Once you have your connections, you should be able to create an SSIS package that moves data between the two.

I ddin't have to move data from SQLServer to MySQL, but I imagine that once the MySQL connection is installed, it works the same as moving data between two SQLServer DBs, which is pretty straight forward.

小开

Using MSSQL Management Studio i've transitioned tables with the MySQL OLE DB. Right click on your database and go to "Tasks->Export Data" from there you can specify a MsSQL OLE DB source, the MySQL OLE DB source and create the column mappings between the two data sources.

You'll most likely want to setup the database and tables in advance on the MySQL destination (the export will want to create the tables automatically, but this often results in failure). You can quickly create the tables in MySQL using the "Tasks->Generate Scripts" by right clicking on the database. Once your creation scripts are generated you'll need to step through and search/replace for keywords and types that exist in MSSQL to MYSQL.

Of course you could also backup the database like normal and find a utility which will restore the MSSQL backup on MYSQL. I'm not sure if one exists however.


提交答案