How to insert multiple tables into their respective backup tables using insert sql?

Question

For the purpose of backup and restore i want few tables to be just backup while performing a restore process. I have created backup tables but now whenever i do restore process it shows:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062      Duplicate entry '1' for key 'PRIMARY':

Actually both tables have same schema and same primary key so whenever it try to update it there is conflict. Can anyone please tell me how to do it ? One more thing can i use drupal Features to do it and if yes please let me how.

Answer 1

You should use

Merge Queries

a Merge query is a combination of an Insert query and an Update query. If a given condition is met, such as a row with a given primary key already existing, then an Update query is run. If not, an Insert query is run.

The above is for drupal

If you are using mysql then you can use UPDATE or ON DUPLICATE KEY UPDATE

Answer 2

I have resolved the issue by doing following thing, hope it may be useful to anyone too.

The cause of the problem is that the parent table and its corresponding backup table have same schema so whenever Restore process is took place it just encounter a problem due to same primary key on both tables. Hence to overcome it,first remove Primary Key from backup table as:

ALTER TABLE $backup_tbl_name DROP PRIMARY KEY, CHANGE $primary_key $primary_key int(11) NOT NULL

and then add a new column 'ID' to all backup tables using :

ALTER TABLE $backup_tbl_name ADD `id` BIGINT unsigned NOT NULL   AUTO_INCREMENT COMMENT 'Auto increment field for backup table.' PRIMARY KEY

and making this newly added column Primary key of backup table.

And at last INSERT query has to be modified as well as during insertion newly added column also has to be pass. And it modified as:

INSERT INTO $backup_tbl_name SELECT * , NULL FROM $tbl;