Page 1 of 1

help merging two tables

Posted: Mon Sep 20, 2004 12:15 am
by crabyars
I have two tables, identical in structure. I'm trying to merge the (unique) data by dumping and loading, but I'm running into "dupicate ID" errors.

Any suggestions? Thanks..

Posted: Mon Sep 20, 2004 1:37 pm
by ldomingues
You are trying to insert duplicate primary keys.

If the primary key is autonumber (and has no dependencies with other tables), I'd recomend dropping the pkey field (in both tables), using "insert into", from table A to table B and re-creating the pkey field.

Posted: Mon Sep 20, 2004 7:06 pm
by crabyars
Hi thanks for the tip. In the end I wrote a small script to do the deed. In case anyone wants to know, here it is:

Code: Select all

<?
//php code snippet to copy all records from one database to another
//requires ez_sql by Justin Vincent
//this script assumed the FIRST field of the target table is the ID key
//which will *not* be  copied
include ('ez_sql.php');
$sourceTable = "sourceTable";
$targetTable = "targetTable";
$results = $db->get_results("SELECT * FROM $sourceTable");
$fieldNames = $db->get_col_info();
$fieldsize = count($fieldNames);
foreach ($results as $result) {
	$dbfields = "";
	$dbdata = "";
	echo("processing..<BR>-----------------------------<BR>");
	for ($i = 1; $i<$fieldsize; $i++){
		echo "fieldName: $fieldNames[$i]<br>";

		if ($i == 1) {
			$dbfields .= $fieldNames[$i];
			$dbdata .= "'".$db->escape($result->$fieldNames[$i])."'";
		} else {
			$dbfields .= ", ".$fieldNames[$i];
			$dbdata .= ", '".$db->escape($result->$fieldNames[$i])."'";
		} 
	}
	echo "<BR>";
	$query = "INSERT INTO $targetTable ($dbfields) VALUES ($dbdata)";
	echo "<BR><BR> \$query = $query<BR><BR>";

//       Uncomment next line to perform the update
//	$db->query($query);
}


?>
Hope this helps someone who might be stuck on the same thing..