Copying the Data - MySQL

After establishing the connections, the next logical step is to move the data from one place to another. This means "SELECT"-ing the data from the source table, and "INSERT"-ing it into the target table. (Assume for now that the target table already exists and has the correct structure to handle the data--we’ll deal with the concept of dynamically building the correct table structures in the next section.)

This can be done with the code shown below (assuming that you’ve created the connections with the code from the previous section). Rather than provide extensive commentary outside of the code, I’ve added commenting in the code where appropriate to show the function of each section.

//  Return a recordset pointer to all rows in the source table
$srcRst = mysql_query("select * from $srcTable;", $srcCnx);
$fieldList = "";
$columns = mysql_num_fields($srcRst);
//  Build the field list for our INSERT statement by iterating through the column names
for ($i = 0; $i < $columns; $i++) {
$tempField = mysql_field_name($srcRst, $i);
$fieldList .= $tempField . ", ";
}
//  Chop off the trailing comma and space (
// I’m sure there are more elegant ways of doing this.)
$fieldList = substr($fieldList, 0, strlen($fieldList) - 2);
//  Iterate through the rows, creating an insert statement for each row
while ($srcRow = mysql_fetch_row($srcRst)) {
$insertSQL = "insert into $tgtTable";
$insertSQL = " ($fieldList)";
$insertSQL = " VALUES ";
// Build a comma-separated value list to match the field list generated above
$insertSQL = "(";
// Iterate through the columns again, this time grabbing 
// the value from each column
// and building a value list.  If values are null, 
// we want to explicitly insert the null
// value, because dates will be set to all zeroes, 
// if they’re set to an empty string
for ($i = 0; $i < $columns; $i++) {
if ($srcRow[$i] == null)
$insertSQL .= "null, ";
else
$insertSQL .= "'" . $srcRow[$i] . "', ";
}
// Again, chop off the comma and space at the end
$insertSQL = substr($insertSQL, 0, strlen($insertSQL) - 2);
$insertSQL .= ");";
// Execute the finished SQL insert statement on the target table
mysql_query($insertSQL, $tgtCnx);
}
//  Free memory from the source recordset.
mysql_free_result($srcRst);

Building the insert statement on the fly is made significantly easier by MySQL’s flexibility with field types when entering data into tables. In my experience with other databases, specifically Access and Visual FoxPro, specific formatting and quoting syntax must be followed for the inserts to work correctly. An ODBC version of this database copying tool would be wonderful, but it would be difficult to deal with the quirks in SQL syntax for each database engine or ODBC driver.

One other thing I should mention is that copying data this way is probably not appropriate for large tables of more than, say, 10,000 rows. To copy larger sets of data, you would likely have to adjust the timeout settings on your web server, if you have access to it. The timeout for most servers is between 30 and 90 seconds before scripts fail due to timeout, and that may not be enough time to copy exceptionally large tables. I’ve successfully tested the script with tables of up to 5,000 rows without any problems.

>>> More MySQL Articles          >>> More By Stephen Junker

blog comments powered by Disqus