Merging Databases

--0015175ce05e3c18290469a802fe
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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@pixelated.net

--0015175ce05e3c18290469a802fe--

RE: Merging Databases

-----Original Message-----
From: Johnny Withers [mailto:johnny@pixelated.net]
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 ha=
s
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 ever=
y
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 dat=
a
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@pixelated.net

---

Why not create a view and just concatenate on an identifier? This way the d=
ata can be kept in the same forms.

Or, if you do want to have it as one table, you can use a select insert sta=
tement 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 read=
y.

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 sen=
d it to you, do not disseminate, copy or otherwise use this e-mail or its a=
ttachments. Please notify the sender immediately by reply e-mail and delet=
e 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Merging Databases

--0015175d06e02bb2750469a965c7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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@oft.state.ny.us> wrote:

>
>
> -----Original Message-----
> From: Johnny Withers [mailto:johnny@pixelated.net]
> 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@pixelated.net
>
> ---
>
> 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=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0015175d06e02bb2750469a965c7--

Re: Merging Databases

On 11-May-09, at 2:09 PM, Johnny Withers wrote:

> 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.


I've done this before to merge separate databases, it will work as
long as you plan it out right. Some things I ran into:

1) If the other database you are copying into is still active make
sure you leave enough room between the current top record and where
you expect to start the new records - plan for a couple of weeks
activity even. Schedules can change, and you may do the update and
dump but then be delayed on the import.

2) Its a good idea to add a where clause to guard against accidental
extra runs, or in the case of foreign keys to avoid rewriting data
inserted since you modified the main table, i.e.

update t1 set pk=pk+100000 where pk < 100000;
update t2 set fk=fk+100000 where fk < 100000;

That's saved me from a few unexpected consequences.

Cheers,
Chris.
-
Chris Clarke
Principal Consultant
C4 Consulting

High performance IT solutions
http://cfourconsulting.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Merging Databases

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 =3D 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@pixelated.net]=20
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@oft.state.ny.us> wrote:

>
>
> -----Original Message-----
> From: Johnny Withers [mailto:johnny@pixelated.net]
> 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@pixelated.net
>
> ---
>
> 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=3Djohnny@pixelated.net
>
>


--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg