Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

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?
0
freezilla
Asked:
freezilla
  • 3
  • 3
  • 2
  • +1
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
You can setup an ODBC driver to MySQL and make that a linked server in your MSSQL and simply script the data across.  Another approach is to export the data from SQL to say XML or CSV format and then import into MySQL from there.

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!
0
 
Jason C. LevineNo oneCommented:
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.
0
 
freezillaAuthor Commented:
I have no idea how to write queries or link the tables.  Any code snippets or walk throughs available?
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
Jason C. LevineNo oneCommented:
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.
0
 
freezillaAuthor Commented:
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?
0
 
Jason C. LevineNo oneCommented:
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.

0
 
Walter RitzelSenior Software EngineerCommented:
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).
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, the ODBC data source is key to most of the solutions.  Instead of recreating the wheel, here is a visual on setting up the import from MySQL.  It is a bit dated, but the concepts are still valid.

http://technet.microsoft.com/en-us/library/cc966396.aspx

Instead of using the MySQL ODBC as the source, you would just set it as the destination, i.e., flip perspective of the article since you are going from MSSQL to MySQL.
0
 
freezillaAuthor Commented:
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now