freezilla
asked on
Convert MSSQL to MySQL for Wordpress
As the title suggests, I want to take my existing MSSQL database and import all of the data into my MySQL database that I'm using with WordPress.
I don't really care about any of the stored procedures and, matter of fact, there's really only one table that I want/need to convert. I just don't know how to do it.
I'd prefer something that works for free, of course, but I see there are a lot of commercially available packages out there.
Any ideas and/or success stories?
I don't really care about any of the stored procedures and, matter of fact, there's really only one table that I want/need to convert. I just don't know how to do it.
I'd prefer something that works for free, of course, but I see there are a lot of commercially available packages out there.
Any ideas and/or success stories?
Just to expand on mwvisa1's comment:
I actually do this with MS Access/MyODBC because my SQL-fu is weak. But by linking the MSSQL and MySQL tables, it's a snap to write queries to port the data over.
I actually do this with MS Access/MyODBC because my SQL-fu is weak. But by linking the MSSQL and MySQL tables, it's a snap to write queries to port the data over.
ASKER
I have no idea how to write queries or link the tables. Any code snippets or walk throughs available?
MyODBC is available from:
http://dev.mysql.com/downloads/connector/odbc/
Once you install it, you have to define a Data Connection to your MySQL table so it will able available through Access.
Once the connection is defined, you create a linked table in Access by right-clicking in the tables section and selecting "Link Tables..." You will need to link your MSSQL tables and your MySQL tables to Access.
From there, you need to create the Insert queries that will take your data from the MSSQL and insert it in the MySQL. Your challenge at that point is that you will have to know exactly how WordPress wants data constructed and the interrelationships between the various WordPress tables.
http://dev.mysql.com/downloads/connector/odbc/
Once you install it, you have to define a Data Connection to your MySQL table so it will able available through Access.
Once the connection is defined, you create a linked table in Access by right-clicking in the tables section and selecting "Link Tables..." You will need to link your MSSQL tables and your MySQL tables to Access.
From there, you need to create the Insert queries that will take your data from the MSSQL and insert it in the MySQL. Your challenge at that point is that you will have to know exactly how WordPress wants data constructed and the interrelationships between the various WordPress tables.
ASKER
I've actually already got an Access DB that's got all of my MSSQL tables linked to it via ODBC. I take it I need to download that from MySQL and essentially do the same thing?
Yes, you link to the tables in the MySQL database in the same Access file as your MSSQL tables. You just need the MyODBC connector to do it.
In this case, the recommendation made by mwvisa1, to use DTS is the best approach for you. But you still need to know how to create a ODBC data source in order to make it work (which is explained in the link).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This did work, though I was able to get access to SQL Studio Management and use the DTS Wizard, so I did it like that as I've gone that route before.
Check out this article:
http://dev.mysql.com/tech-resources/articles/migrating-from-microsoft.html
They speak about Microsoft DTS which allows you to do export through ODBC to MySQL. That is pretty much what I was getting at with the linked server approach and is probably more appropriate for a one time migration whereas that is what I use for long-term sharing of data.
The text import/export is along the lines of what I was referring to on the XML/CSV approach. They offer some tutorial on this and you can find some articles in my profile on how to parse CSV/XML data in MySQL if you need further assistance.
Hope that helps!