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
> 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.
High performance IT solutions