在一端将所有行从一个表插入另一个+时间戳

Question

I'm creating a change log db that's the exact same as my active db except it has a changedate DATE field at the end.

The db is basically one primary key id, and about 50 other columns of various data types. The script I have in php is it tries to insert new ids and if it gets the error message for duplicate primary key, then it should get that row, insert it into my backup db with a curdate() call as the final date value, delete the entry from my first db, then insert the new entry.

I have all the other parts of the script finished except the part where I have to insert everything from the first table + an extra column for curdate(). Or if there's a better solution to my problem of inserting into a backup database when a duplicate primary key comes in when there's a fairly high amount of rows please share that.

Answer 1

You could do an INSERT INTO SELECT:

INSERT INTO `backupTable` SELECT *, NOW() FROM `originalTable` WHERE id = '$id';

You have to specify the ID for the entry you wish to copy to your backup db. You have also to be sure, that the IDis not already in your backup table. You can use REPLACE INTO to workaround this case.

REPLACE INTO `backupTable` SELECT *, NOW() FROM `originalTable` WHERE id = '$id';