How do I export a database so I can merge it with another?

I have two databases with identical structures. They are named the same but have different data. I want to export the data from the one and import it into the other. The problem I keep having is that the "id" field which is a duplicate entry.

Is there a way to export one without the id field? How can I do this?

 

There are many ways you can do this.

create table export_table as select all,fields,except,id from source_table

Then export the "export_table". You could also load the two tables you want to merge, and use "create table as" in combination with the "select into" queries to load your "merge table". Then perform the dedupe, reindex the table and you're off!

Alternatively, you could create your temporary export tables (sans primary key), export the data and perform the merging in excel (or even using command-line tools if you've got access to a bash shell). I'd recommend doing your data manipulations in the database itself.

Hopefully this makes sense, I can clarify this process if you want to go into more detail.

 

How do you export the databse without the primary key? That seems to be the easiest. Then I can just import it into the other, right?

 

Use the "CREATE TABLE EXPORT_TABLE AS SELECT ALL,FIELDS,MINUS,PRIMARYKEY FROM SOURCE_TABLE".

This will create a temporary table, with all fields (except the primary key), all fields will match the data-types from the original table and it will be unencumbered by table & field constraints (including the enforcement of primary key constraints, e.g. not null & unique). This is the table you'll work with (and export to a text file using mysqldump or phpmyadmin if that is what you need to do).

Does this make it more clear?

 

In that "AS SELECT ALL,FIELDS,MINUS,PRIMARYKEY" Do i just list the fields there?

 

Yes. Example:

Say your table looks like this:

CREATE TABLE IF NOT EXISTS `wp_users` (
  `ID` bigint(20) unsigned NOT NULL auto_increment,
  `user_login` varchar(60) NOT NULL default '',
  `user_pass` varchar(64) NOT NULL default '',
  `user_nicename` varchar(50) NOT NULL default '',
  `user_email` varchar(100) NOT NULL default '',
  `user_url` varchar(100) NOT NULL default '',
  `user_registered` datetime NOT NULL default '0000-00-00 00:00:00',
  `user_activation_key` varchar(60) NOT NULL default '',
  `user_status` int(11) NOT NULL default '0',
  `display_name` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`ID`),
  KEY `user_login_key` (`user_login`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Code (markup):

Your query to create the temp export table would look like this:

CREATE TABLE exporttable AS SELECT
 user_login,
 user_pass,
 user_nicename,
 user_email,
 user_url,
 user_registered,
 user_activation_key,
 user_status,
 display_name
FROM wp_users

Code (markup):