How to insert multiple tables into their respective backup tables using insert sql?
Solutions
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
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;