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

From: Bill <edison_at_newpaltz.edu>
Date: Wed, 08 Nov 2006 08:58:40 -0500
Message-ID: <12l3oi7qsccvi60_at_corp.supernews.com>


DA Morgan wrote:

> 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.

Actually I took another look at this. Unfortunately, it can't be done using SQL*Plus.
> 
[Quoted] > 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. 

"Benign" in terms of security.
What you seem to be considering will guarantee the [Quoted] > 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.

  That's because you don't appear to understand it.

take care,
b Received on Wed Nov 08 2006 - 14:58:40 CET

Original text of this message