List:General Discussion« Previous MessageNext Message »
From:Chris Clarke Date:May 11 2009 9:19pm
Subject:Re: Merging Databases
View as plain text  
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
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