Using PHP to Copy an SQL Server Table to MySQL

by Curtiss   |   last updated January 7, 2010   |   PHP Tutorials ·

At work, we have a scheduled task run every day that pulls information out of a huge Oracle database and dumps it into a Microsoft SQL table on one of our local IIS Web servers. We then have a script on our remote LAMPP Web server (the one we use to serve up our public Web site) that queries that MSSQL table and displays results on the Web site.

Unfortunately, because the Web server and the MSSQL server are in different locations, the results are sometimes slow and unreliable. For instance, if, for some reason, the network connection to our IIS server is down or if the IIS server itself is down, we can’t pull any data from the MSSQL table.

The other day, I began working on a script that will pull only the information we’ll need for any given day out of the MSSQL table and then create a new table in our MySQL installation that resides on the same server as our Web site.

Following are some good tips for you if you decide to do the same thing.

First of all, you will need a way to get some information about your MSSQL table and its columns. To do that, I found the following query.

SELECT
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{$table_name}'
ORDER BY ORDINAL_POSITION ASC

That query will pull the name of the column, the data type and the maximum length of the column (if applicable) from the columns in your MSSQL table.

Next, you’ll want to start building the queries that will create your new MySQL database and table. Following are some good places to start.

$cols = array();
while($r = mssql_fetch_assoc($sql,$mslink)) {
    $cols[$r['COLUMN_NAME']] = $r['COLUMN_NAME'].' '.$r['DATA_TYPE'];
    $tmplen = trim($r['CHARACTER_MAXIMUM_LENGTH']);
    $cols[$r['COLUMN_NAME']] .= (!empty($tmplen)) ? '('.$tmplen.')' : '';
}
mssql_free_result($sql);
$sql = "CREATE DATABASE $dbname";
if(mysql_query($sql,$mylink)) {
    $sql = "CREATE TABLE $tname ";
    $sql .= implode(",\n",$cols);
    /* If you have specific indices to add, you can add a line of code here to do that. */
    $sql .= ')';
    /* print("We are preparing to run the following query to create the new table:\n".$sql); */
    if(mysql_query($sql,$mylink)) {
        print('We successfully created the table.');
    }
    else {
        print('There was an error creating the table. '.mysql_error());
    }
}
else {
    print('There was an error creating the database. '.mysql_error());
}

Your new table should be created, now. You should note, however, that MS SQL does offer some data types that differ from those offered in MySQL. Therefore, if your MS SQL table includes any columns with data types that aren’t compatible with MySQL, you may need to write a function to handle that. For instance, the MS SQL “currency” data type may need to be converted to a decimal or float data type in your new MySQL table.

You probably want to actually copy the data now, though. In my next article, I will show you an easy way to do that.