Transform MySQL 'update' statements to 'insert' statements

I have an 1开发者_Python百科1MB dump containing UPDATE statements, formatted like this:

UPDATE `table` SET `id` = 1,`field`='etc' WHERE `table`.`id` = 1;

However, I need to insert these values into a new database, using statements like this:

INSERT INTO `table` (`id`, `field`) VALUES ('etc', 1);

Has anyone written a tool or service to convert UPDATE statements to INSERT statements? (At first glance, it seems to be just beyond the reach of regex)

Lazy approach. If you have the ids, insert no matter what for those id's and after that run the updates.

Nothing is beyond the reach of a regex. Foreach line in the dump, use the following :

Pattern :       ^.*`id` = (\d+).*`field`='(.*)'[^']*WHERE.*$
Replacement :   INSERT INTO `table` (`id`, `field`) VALUES ('$2', $1);

This simple regex search and replace in notepad++ will convert any update into an insert:

Search for: UPDATE (.*) SET (.*) WHERE .*$
Replace with: INSERT INTO \1 SET \2;

This assumes that what followed the "where" was originally a unique key. It changes it so that key will auto-increment when you insert.