List:General Discussion« Previous MessageNext Message »
From:Andrew Braithwaite Date:May 11 2009 9:26pm
Subject:RE: Merging Databases
View as plain text  
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.

Andrew

-----Original Message-----
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
renumber
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
add
the same # to the table1_id columns in any other table. After we do
this, we
could do the select from.. insert into method I suppose.

-jw

On Mon, May 11, 2009 at 2:49 PM, Weston, Craig (OFT) <
Craig.Weston@stripped> wrote:

>
>
> -----Original Message-----
> From: Johnny Withers [mailto:johnny@stripped]
> Sent: Monday, May 11, 2009 3:30 PM
> To: MySQL General List
> Subject: Merging Databases
>
> Hi,
> I have a lot of databases that have the exact same tables and each
table
> has
> the exact same column structure. I'm looking at merging two of these
> databases together into a single database (Company A bought Company B
and
> wants the data from A combined into B now).
>
> I've been tossing around the idea of looking in database B at each
table
> that would need to be "merged" and simply adding the last ID number to
> every
> ID number in database A's tables. For example, in table1 in B's data,
the
> 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
A's
> data
> and import (LOAD DATA) into B's data?
>
> Has anyone done something like this before? Did you have problems?
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@stripped
>
> ---
>
> Why not create a view and just concatenate on an identifier? This way
the
> data can be kept in the same forms.
>
> Or, if you do want to have it as one table, you can use a select
insert
> statement to move from one to another. Build the select query first to
get
> the data looking like you want it, then convert it when you think you
are
> ready.
>
> Of course, backups are your friend in any case.  :)
>
>
> Cheers,
> Craig
>
>
> This e-mail, including any attachments, may be confidential,
privileged or
> otherwise legally protected. It is intended only for the addressee. If
you
> received this e-mail in error or from someone who was not authorized
to send
> it to you, do not disseminate, copy or otherwise use this e-mail or
its
> 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:
http://lists.mysql.com/mysql?unsub=1
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped
Thread
Merging DatabasesJohnny Withers11 May
  • RE: Merging DatabasesOFT)11 May
    • Re: Merging DatabasesJohnny Withers11 May
      • Re: Merging DatabasesChris Clarke11 May
      • RE: Merging DatabasesAndrew Braithwaite11 May