We help IT Professionals succeed at work.

MSSQL to MySQL Transfer

Hi Experts,

I need to transfer a database from MSSQL to MySQL, one table at a time...

Does MSSQL or SQL Server Management Studio has such a function to produce for me a CREATE TABLE and INSERT scriots that I can just run under MySQL, like phpMyAdmin does, or do I have to create m,y own.

If I have to do it, then  I guess the simplest way for me to do it is use linked tables in Access, then use the tables and fields collections, but if there an easier way?

Thank you
Comment
Watch Question

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
see: http://www.mysql.com/products/workbench/migrate/
Most dbms products have migration tools to help migrate INTO their product

So you don't have to create your own scripts (and there will be more available than just the above) but migrations typically aren't  simple (but this depends on the complexity of the databases).
Distinguished Expert 2017
Commented:
Assuming the DDL to create tables is the same or similar in MySQL to SQL Server, then you can use SQL server to generate the create statements.  Once the MySQL database is created, you can use Access to link to both databases and use append queries to copy from SQL Server to MySQL.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
If you're looking for something that's ready to go, then you might consider Convert-IN. I've used the products from Convert-IN in previous projects, and they work very well, and are inexpensive (around $50 US). I converted from Access to MSSQL and MYSQL, and each went off without a hitch. There was still work to be done on the FE, of course, but the database conversion was done with just that tool.

http://convert-in.com/sql2mss.htm
APD TorontoSoftware Developer

Author

Commented:
Pat, assuming that I have created my table structures in MySQL, how would you use SQL to generate INSERT statements?
Distinguished Expert 2017

Commented:
Link to tables in both databases.  Create a query that selects data from the source system.  Change the query type to append and choose the target table.  run the query.  Access will transfer the data.  It doesn't generate an Insert statement for each row being transferred.  It just does the transfer.  Or, you could export to a .csv or .txt file and then import that into the other database.
APD TorontoSoftware Developer

Author

Commented:
PhpMyAdmin has a function to export table data as SQL query, which produces an INSERT statement for each row. My question is can that be done with any SQL or SQLServer Management Studio?
Dave BaldwinFixer of Problems
Most Valuable Expert 2014
Commented:
You might try Adminer http://www.adminer.org/ .  Version 3/4 is supposed connect to MS SQL but it crashes on my old computer.