Merging two Drupal instances into a single database

I recently had to transfer some tables from a Drupal site that had been developed in it's own database, into an existing Drupal database. In order to do this, the MySQL dump needed to be edited to add table prefixes, lest the existing Drupal install be wiped out.

Editing this by hand was out of the question, but thanks to the emacs replace-regexp command, I was able to change the entire dump file in a matter of minutes.

To initiate the command, one hits ALT-x and types in replace-regexp. First, enter the pattern to find. To add a prefix to all the DROP TABLE commands:

DROP TABLE IF EXISTS `\(.*\)`

The \(.*\) is the regular expression that tells emacs to match everything inside those parenthesis.

After entering the search, one is prompted to enter the replacement pattern. To add a prefix, for example myprefix to the DROP TABLE commands I used this pattern:

DROP TABLE IF EXISTS `myprefix_\1`

The \1 pulls in whatever was matched in the first set of parenthesis in our search pattern.

I repeated a similar search and replace for the other commands that reference a table in a typical Drupal database:

 CREATE TABLE `\(.*\)`
 CREATE TABLE `myprefix_\1`
 
 LOCK TABLES `\(.*\)`
 LOCK TABLES `myprefix_\1`
 
 ALTER TABLE `\(.*\)`
 ALTER TABLE `myprefix_\1`
 
 INSERT INTO `\(.*\)`
 INSERT INTO `myprefix_\1`

After that I was able to import this dump file into the existing Drupal database (without wiping out the other installation) and continue development as a multi-site sharing a single database.