Copying a database record with relations

Question

I have 2 sets of tables in the same database - A live set and a test set. Each set has two tables in it - table A and table B - with a one-to-many relationship between them.

What I need to do is to select certain records from table A in the test set and copy the records in their entirety, along with their relations in table B, into the live table set. The structure of the sets is identical.

is it possible to do this without having to break the records up manually?

I'm using the doctrine ORM (1.2 I think) in the context of the symfony 1.4 PHP framework.

So far I've been trying something like this:

$record = Doctrine_Core::getTable('testSetTableA')->find(1);
$liveSetTableArecord = new LiveSetTableArecord();
$liveSetTableArecord = $record->copy();
$liveSetTableArecord->save();

But I get the feeling that I'm missing something fundamental. As far as I can tell, there is no method for setting a record in it's entirety from a query object?

you can post your solution as an answer and accept it if you think it is a good way out of this problem. — greg0ire

David Tuite David Tuite · Accepted Answer · 2011-01-25T12:55:17

I did more of my own research into this problem and as far as I can figure out, using the ORM for this type of operation is just a bad idea in the first place because of all the unnecessary overhead involved.

It's far more efficient just to use a raw "INSERT INTO" statement which is what I'm doing now.

    $connection = Doctrine_Manager::connection();
    $query = "INSERT INTO Set2tableA SELECT * FROM Set1tableA WHERE id = '$id' ON DUPLICATE KEY UPDATE Set2tableA.id = Set2tableA.id";
    $statement = $connection->execute($query);
    $statement->execute();

    $query2 = "INSERT INTO Set2TableB SELECT * FROM Set1TableB WHERE tableA_id = '$id' ON DUPLICATE KEY UPDATE Set2TableB.id = Set1TableB.id";
    $statement = $connection->execute($query2);
    $statement->execute();

The 'ON DUPLICATE KEY UPDATE' is necessary because the primary key in table B is non-null and therefor when you try to copy record 1 from table A into Table B mysql finds that there is already an entry with ID=1 and throws an error.

Copying a database record with relations

1 Answers