Re: replace of sql*loader

"Bing Du" <bing-du_at_tamu.edu> wrote in message news:3AEDC955.C3321110_at_tamu.edu...
> Hi,
>
> I'm just not sure how 'replace' works.
> Say, one of my Oracle tables contains the following two entries:
>
> student_id email address
> 111 user1_at_tamu.edu
> 222 user2_at_tamu.edu
>
> Student_id is the primary key.
>
> And the external data feed file (data.txt) only has the following one
> entry.
>
> 222 user2_at_dept.tamu.edu
>
> If I use data.txt to refresh the table by using 'replace' in the control
> file, will it:
>
> 1) leave the record '111 user1_at_tamu.edu' as it is and just change the
> entry from '222 user2_at_tamu.edu' to 222 user2_at_dept.tamu.edu'?
> 2) delete '111 user1_at_tamu.edu' and update '222 user2_at_tamu.edu'
>
> 1) or 2) which is correct? The option 1) is what I want. If 'replace'
> can not do it, how can I do 1)?
>
> Thanks,
>
> Bing
>

1 replace in sql*loader *wipes* the table! 2 sql*loader also *never updates*. If appropiate primary key constraints are present, existing rows simply will be rejected. If they don't exist, new records will be created.

One of the guidelines as to good policy in loading external data, is *never* load *directly* into *production tables* In your case you need to setup a separate technical table which is an exact copy of your data.txt, and write a script to process the updates contained in the technical table/data.txt.

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Apr 30 2001 - 22:51:24 CEST