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

[Database Administrator]

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

[Database Administrator]

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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

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@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

Thanks for tip.
Bill

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

[Database Administrator]

Thanks for tip.
Bill

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

[Database Administrator]

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

[Database Administrator]

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

[Database Administrator]

Bill wrote:
>
>
> 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.
>>
>> 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
>> 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

Au contraire.

It is because I have made those mistakes in the past and paid the price.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

Bill wrote:
>
>
> 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.
>>
>> 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
>> 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

Au contraire.

It is because I have made those mistakes in the past and paid the price.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

DA Morgan wrote:
> Bill wrote:
>
>>
>>>>
>> That's because you don't appear to understand it.
>>
>> take care,
>> b
>
>
> Au contraire.
>
> It is because I have made those mistakes in the past and paid the price.

Well, actually you appear to still be paying the price.