Re: Conversion of Location Codes with Foreign Keys

From: Ed prochak <ed.prochak_at_magicinterface.com>
Date: 29 Apr 2004 13:51:32 -0700
Message-ID: <4b5394b2.0404291251.13b9e8cf_at_posting.google.com>


michael.dietz_at_gmx.de (Michael) wrote in message news:<e92fb27e.0404282210.1ae418d_at_posting.google.com>...
> I have to do a data migration for a client. The following conversion
> is required.
>
> Task:
> - MOVEMENT table with 7,000,000 records, each has a location code
> - Location code has changed; I have a mapping table with the old
> location code and the new code
>
> Issues:
> - The conversion should be done in the same table as I do not want to
> move the 7,000,000 records from one table to another if not necessary
> - The table has a foreign key to a LOCATION master; this means that I
> might have to update LOCATION first; however, I cannot just convert
> the codes in LOCATION as this would trigger foreign key violations in
> the MOVEMENT table; I also cannot just add the new locations because
> some new codes might be the same as existing old codes (e.g. the new
> code 'PDP' for 'Purchasing Department' could be the same as the old
> 'PDP' for 'Public Relations Department'.
>
> Does anybody know what the best approach would be here?
>
> Michael

drop or disable the constraint,
truncate the LOCATION master,
load the LOCATION master,
convert the MOVEMENT table,
restore the FK constraint
(deal with any constraint violations)

It should not be hard to do.

    ed Received on Thu Apr 29 2004 - 22:51:32 CEST

Original text of this message