Copy data from SQL Server to Mysql

Question

I have a MS SQL database, that i want to copy to a mysql database, its a very large database, with a table having about 2 million rows, Currenlty i am copying it using code in C#, that uses datareader to loop through all rows in SQL table and inserts in a mysql table, but this is taking very long time, is there any other alternative to copy database, ? both databases mysql and ms sql have same tables structure.

Please advice, Thanks

Try to generate INSERT statements to add multiple rows at once - INSERT INTO table VALUES(1,2,3),(4,5,6),... — Devart, Apr 10 '12 at 14:50
How often do you need to do this? If it is once, then why does it matter how long it takes. If it is multiple times, why copy the whole database? - you could copy just the records that changed. — D Mac, Apr 10 '12 at 15:11

score 2 · Answer 1 · answered Apr 10 '12 at 15:18

Depending on the SQL Server edition you have available, you can use Integration Services, that handles the reading from SQL Server, type conversions, and writing to the target MySQL. It's optimized to do it as quickly as possible.

And it's not too hard to learn to use it.

You simply have to create a package, with a source (SQL Server), a destination (MySQL) and the extra components that you can find useful (probably only Change Type).

score 0 · Answer 2 · answered Apr 10 '12 at 14:43

0

You can use the

MySql migration Toolkit

or

MSSQL2MySQL

Haven't used either personally but they are a couple of the toolkits available.

answered Apr 10 '12 at 14:43

Brian

2,229
17
24

there is nothing in mysql migration tool kit that can be used to copy data from ms sql to mysql .. correct me if i am wrong.. – Ishti Apr 10 '12 at 14:58
as I said, I haven't used either but according to this post on SO, http://stackoverflow.com/questions/129/how-to-export-data-from-sql-server-2005-to-mysql many people have used it successfully. – Brian Apr 10 '12 at 15:43

score 0 · Answer 3 · answered Apr 10 '12 at 15:22

Much like the other answer, there are a number of ways you could go about doing this. The first thing that popped into my head was using SSIS to migrate the data.

http://www.packtpub.com/article/mysql-data-transfer-using-sql-server-integration-services-ssis

score 0 · Answer 4 · answered Apr 10 '12 at 15:55

0

You could also look into Talend Open Studio. I use TOS to migrate data from several databases, including MS SQL, to a MySQL database. Its easy to learn and easy to maintain.

answered Apr 10 '12 at 15:55

BRM

201
1
4

score 0 · Answer 5 · answered May 01 '12 at 11:53

I dunno if you are willing to pay for that(I think you should in such scenario) I would recommend using DBSync for MS SQL & MySQL. I had the task to migrate a huge 25Gb database from SQL Server to MySQL and this little program (only 17Mb compared to 277Mb Talend Open Studio ) works fine.

It s deadly simple to use , simply follow the steps (5 steps) and you go your database migrated. I have tried Talend Open Studio and the documentation is not good at all and there is a long learning curve there whereas with DBSync, if you ever installed a software on you PC then you can also do it very easily. Also if you one of those who like testing before buying, you can try the free version which can at least migrate the 50 first rows of all tables in a database. I would defiantly give is a go and if you are dealing with a huge amount of sensible data,$99 is a treat. if you find any simpler or faster way to do it please let us know cause I often migrate databases and I would appreciate if you d have any better.

just tried Brian's solution who suggest MySQL Migration toolkit. It is free and it does the job better :-) quiet happy thumb up brian ;-) — webicy, May 01 '12 at 17:21

Copy data from SQL Server to Mysql

5 Answers5