Copying Data from SQL Server to MySQL

by Curtiss   |   last updated December 21, 2009   |   PHP Tutorials ·

In my last post, I showed you a way to copy the structure of an SQL Server table into a new MySQL table. In this post, I will show you how you can fairly easily copy the data from that SQL Server table into your new MySQL table.

Before we start, we should make sure we have a complete list of the columns in our MySQL table (which should, if we did everything right in building the table, be identical to the columns in our MS SQL table).

	if(!isset($cols) || empty($cols)) {
		$cols = array();
		$sql = "SHOW COLUMNS FROM $tname";
		if($sql = mysql_query($sql,$GLOBALS['lflink'])) {
			while($row = mysql_fetch_assoc($sql)) {
				$cols[$row['Field']] = $row['Type'];
			}
			mysql_free_result($sql);
		}
	}
	$curid = NULL;

To begin with, I’ll start building the INSERT query that we’ll run on our MySQL table.

	$insertsql = "INSERT INTO $tname (".implode(",\n",array_keys($cols)).") VALUES(\n";
	$insertlist = array();

Next, we’ll write and execute the query that begins pulling data from the SQL Server table. In the code below, I’ve included two properties that I’m using to filter the data we’re copying. After all, if you don’t need all of the data that’s in the SQL Server table, there’s no point in putting it in our MySQL table. You will obviously need to edit the WHERE clause before running this code yourself.

	$sql = "SELECT * FROM {$tname} WHERE col1 >= ".PROPERTY1." AND col2 <= ".PROPERTY2." ORDER BY col1, col2 ASC";
	$sql = mssql_query($sql,$mslink);

From there, we’ll begin looping through the results of our MS SQL query and store the data into an array that we can use to build our MySQL INSERT query.

In the code below, I refer to a column with the name of “ID_FIELD.” You will want to replace that with the name of the column you’re actually using as your primary key. In my case, the SQL Server table actually has three or four identical copies of each row because of the way the script importing the information from the Oracle database works. I am using that code to eliminate those duplicates before we insert the information into the MySQL table. You most likely won’t need that part of the code, but I’ve left it in just in case.

	while($rs = mssql_fetch_assoc($sql)) {
		$insertvals = array();
		if($curid != $rs['ID_FIELD']) {
			foreach(array_keys($cols) as $k) {
				$rs[$k] = (is_numeric($rs[$k])) ? $rs[$k] : "'".mysql_real_escape_string($rs[$k])."'";
				$insertvals[$k] = $rs[$k];
			}
			$insertlist[$rs['ID_FIELD']] = implode(',',$insertvals);
		}
		$curid = $rs['ID_FIELD'];
	}
	$insertsql .= implode("),\n(",$insertlist).")";
	mssql_free_result($sql);

Finally, we’ll run the new MySQL query to insert all of the information into our new MySQL table.

	if(mysql_query($insertsql)) {
		echo 'The data was added to the table successfully.';
	}
	else {
		print('There was an error: '.mysql_error().'
'.$insertsql.'
');
	}

That’s all there is to it. By combining all of that code with the code I showed you in my last post, we should have successfully copied the structure and data from an SQL Server (Microsoft SQL) table to a brand new MySQL table.

Because we are storing all of the data retrieved from the SQL Server table in a PHP array, you may need to actually run this code in parts (pulling smaller portions of the data at a time and inserting it into the MySQL table) so you don’t overflow the amount of memory allocated to your PHP processor. However, if the data you’re pulling from the SQL Server table is small enough to fit in your memory buffer (as it is in my case), you can run it all at once without any worries.