Re: data migration question. source & target tables are the same-

Bill wrote:
> I'm doing a one time task of migrating some data from one oracle
> database into another, bigger, more centralized database in order
> to consolidate things and make access more convenient. Source and
> target table definitions are mirror images. However, I'm not sure of the
> best way to proceed with the 2 step process:
>
> 1: The source table, which has been in use for a 6 or 8 months, now
> has about 50,000 records. Before the migration, 2 fields in each source
> table record will need to be populated using data items appearing in
> other tables of the target database. (we will be ousting the source
> table key of SSN, replacing it with a more benign key item). The second
> field is another, internal key, to be used as a future cross reference.
> Both tables also store the SSN in an alternate field, in case it will be
> needed for future internal verification purposes (so right now at this
> point, the source table actually has 2 fields populated with SSN data.
> One of these will be replaced with another key during the migration).
> SSN data can also be pulled from at least one other table in the target
> database, so it can somehow be used for cross referencing purposes.
>
> 2: Once these two fields have been replaced, a 'migration wizard'
> will be used to move the data.
>
>
>
> Step 2 is cake, but I'm not sure how to proceed with step 1. Is there a
> shareware or other oracle tool that will let me do this? I believe this
> can be done with MS Access but I'm not familiar enough with MS Access
> to do it-
>
>
> Hope the weekend was good-
> Bill

Given only 50K records one could accomplish step 1, using SQL*Plus, in somewhere between 5 and 10 minutes.

But I do take exception to your phrase "benign key item." I'll grant that an SSN is not the perfect natural key but given only 50K records it is close enough. What you seem to be considering will guarantee the introduction of corrupt data or force you to use a unique index to, in effect, recreate the original key. It sounds like a very bad idea indeed.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org