Merge MySQL Dump with Existing Table

3 Views

A user of a WordPress site with a Form plugin accidentally delete ALL of the entries for a specific form.

I went into the daily backup and I have a .sql file which has all of the data for the table that the form info is stored.

Read More

Now I need to merge that back into the database, but the dump uses INSERT INTO and stops immediately with an error because most of the entries already exist.

I tried using “ON DUPLICATE KEY UPDATE id=id”, but it ignored everything.

I’ve been searching here and on Google for a couple hours without any kind of solution.

The basic of the dumps is: 

LOCK TABLES `wp_frm_items` WRITE;
INSERT INTO `wp_frm_items` (`id`, `item_key`, `name`, `description`, `ip`, `form_id`, `post_id`, `user_id`, `parent_item_id`, `updated_by`, `created_at`, `updated_at`) VALUES (2737,'jb7x3c','Brad Pitt','a:2:{s:7:"browser";s:135:"Mozilla/5.0 (iPhone; CPU iPhone OS 6_1_3 like Mac OS X) AppleWebKit/536.26 (KHTML, like Gecko) Version/6.0 Mobile/10B329 Safari/8536.25";s:8:"referrer";s:38:"http://mysite/myform/rn";}','192.168.1.1',6,0,NULL,NULL,NULL,'2013-06-30 15:09:20','2013-06-30 15:09:20');
UNLOCK TABLES;

ID #2737 exists, so I either want to ignore it or just update the existing table.

Seems like there would be an easy way to import data from a MySQL dump into an existing database.

ps. I’m trying to do this in phpMyAdmin

Post Views: 3

Related posts

Leave a Reply

1 comment

  1. If the data has not changed for those rows, you can use REPLACE instead of INSERT.

    If you want to skip rows, one possibility is to use a temporary table. Load the rows there and DELETE those rows that have a id that exists in the old table. 

    DELETE FROM my_new_temptable AS temp WHERE temp.id IN (SELECT id FROM wp_frm_items)

    Then just insert the remaining rows into wp_frm_items.

    Or you can move the new rows to a temporary table before restoring from the dump and copy them from there back into original table. There are many possibilities.

    Also, many SQL tools have table merging capabilities.