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=arch...@jab.org
