Table of Contents

SQL Server to MySQL

Converting SQL Server content to MySQL table dumps and inserts

Because Typo3 uses MySQL all the time, I'll now look into filling MySQL database tables with content derived from MS SQL Server.

Extracting data from SQL Server

Do not use the almost worthless Enterprise Manager. Instead, use an MS Access “project” to connect to the SQL Server. Now, right-click on a table name, and select Export. Choose “Save as” > “Text files”.

Use the “Advanced” button to select a few extra options:

  • separation sign should be comma;
  • decimal symbol should be period;
  • text marker should be double quote sign (”).

Use Crimson text editor to prepare data

Use Crimson (or some such editor) to place the string ”),(” at all line ends (without the quotes). Use the find and replace command; next to the Find field you should be able to select “End of Line”. Yes, the ”(” will be put on the previous line, but MySQL does not care about that.

Don't forget to add the INSERT statement at the beginning of the sql file that you are preparing. Use phpMyAdmin to actually insert the data.

WARNING about exporting directly from SQL Server

If you export directly from SQL Server, the Export function is next to worthless. If you have fields exceeding 256 characters they WILL be truncated (a complete result line can be longer than 256 characters, but apparently the export function cannot handle blob type fields properly).

So, copy your tables to MS Access first, THEN use the export function!

Alternative route: use Access2MySQL

You can also buy a program called Access2MySQL Pro, which allows you to convert a complete MS Access database to MySQL. It's not free, however: the registration fee is about 55 dollars, last time I checked.

Of course, if you're running your database in MS SQL Server, you first need to get your data into an MS Access file database:

  • First, use Access to make a “project” file for you SQL Server database (adp file).
  • Then make a new mdb file (an Access database)
  • Inside your mdb Access database, choose the import function and select the adp file to retrieve the data from SQL Server.

On the MySQL server, first make a new database. For maximum internationalization options, use the utf8 character set with utf8-general-ci collation.


Personal Tools