If you are merging table A and table B and say, table A's auto-increment
id is up to 2000, just pick a nice round number like 3000 and add it to
the auto-increment ID column of table B with something like this:
UPDATE tableB SET id = id + 3000;
Then do the same to all the fields in other tables that link to tableB's
auto-increment ID column.
Once that's done, merge the tables with something like:
INSERT INTO tableA SELECT * FROM tableB;
And do the same with the other tables (if they have their own
autoincrement ids then you should leave that out of the insert into
select from (unless those auto-increment ids are referenced by other
tables in which case you'll have to do the same thing cascading down
levels of referential id columns..)
Should do the trick.
From: Johnny Withers [mailto:johnny@stripped]
Sent: 11 May 2009 22:10
To: Weston, Craig (OFT)
Cc: MySQL General List
Subject: Re: Merging Databases
We don't want to use a view because then this database will not
be consistent with the others.
We can't simply use a select from .. insert into because when we
table1's ID column, items in table2 and 3 and so on may link to the ID
column in that table. So we need to update the ID column in table1, then
the same # to the table1_id columns in any other table. After we do
could do the select from.. insert into method I suppose.
On Mon, May 11, 2009 at 2:49 PM, Weston, Craig (OFT) <
> -----Original Message-----
> From: Johnny Withers [mailto:johnny@stripped]
> Sent: Monday, May 11, 2009 3:30 PM
> To: MySQL General List
> Subject: Merging Databases
> I have a lot of databases that have the exact same tables and each
> the exact same column structure. I'm looking at merging two of these
> databases together into a single database (Company A bought Company B
> wants the data from A combined into B now).
> I've been tossing around the idea of looking in database B at each
> that would need to be "merged" and simply adding the last ID number to
> ID number in database A's tables. For example, in table1 in B's data,
> last ID number is 2000, could we simply add 2000 to every ID number in
> table1 in A's data? Could we then export (SELECT INTO OUTFILE) from
> and import (LOAD DATA) into B's data?
> Has anyone done something like this before? Did you have problems?
> Johnny Withers
> Why not create a view and just concatenate on an identifier? This way
> data can be kept in the same forms.
> Or, if you do want to have it as one table, you can use a select
> statement to move from one to another. Build the select query first to
> the data looking like you want it, then convert it when you think you
> Of course, backups are your friend in any case. :)
> This e-mail, including any attachments, may be confidential,
> otherwise legally protected. It is intended only for the addressee. If
> received this e-mail in error or from someone who was not authorized
> it to you, do not disseminate, copy or otherwise use this e-mail or
> attachments. Please notify the sender immediately by reply e-mail and
> delete the e-mail from your system.
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: