Re: Transferring Access data to Oracle

Moorthy:

It depends on how much incremental data is added each week. If there is a lot, then you may want to drop and recreate the Oracle table and reload the data every week. However, if there are only a relatively few new rows to load, then I would load only those new rows into the existing Oracle table. The downside, however, is that you must "flag" those items which have already been copied over, i.e. in the Access table, you must create another column with a "Y/N" field or "Date_Xferred" field to identify those which have already been copied over.

In summary:

Method 1: Full replication each week
+ very straightforward (mindless); easy to script - recreating/reloading tables and recreating indices can be very time-consuming for a large table

Method 2: Incremental update each week
+ takes less time to load/index data (for large datasets); also easy to script
- requires slightly more attention (track rows have been copied, which may increase the chance for human error) +/- requires more space in Access table (additional flag column), but maybe this information would be useful from an audit standpoint

As for the "-" in method 2, you should have a validation report to compare the data (in summary form) of both tables anyways, so this concern may be less relevant.

-Peter

On Fri, 24 May 1996 13:24:15 GMT, moorthy_at_atl.mindspring.com (Moorthy N. Rekapalli) wrote:

>Hello There,
>
> I need to transfer Microsoft Access data to Oracle. I am
>thinking of exporting Access tables to a flat file and then using
>Oracle's SQL*Loader.
>
>Access -> Export to flat file -> SQL*Loader -> Oracle.
>
>In the mean time, people will add more data to Access. I am thinking
>of transferring data at regular intervals like once in a week. To do
>this, first I have to delete all the existing data in Oracle tables.
>I would like to know:
>
>1) Are there any better ways of doing this.
>
>2) Will there be any problems by deleting all the data from Oracle and
>Loading data again.
>
>3) I am also thinking of dropping the Oracle tables and recreating
>them before loading data. Will this cause fragmentation or any other
>problems.
>
>THANKS very much.
>
>Moorthy
>(moorthy_at_mindspring.com)
>
Received on Sat May 25 1996 - 00:00:00 CEST