help merging two tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

help merging two tables

Post 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..
ldomingues
Forum Commoner
Posts: 41
Joined: Fri Aug 06, 2004 1:15 pm
Location: Portugal

Post 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.
crabyars
Forum Commoner
Posts: 37
Joined: Thu Jun 17, 2004 8:24 pm

Post 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..
Post Reply