Re: Sqlldr Query

Hallo Daniel,

Thank you for replying. I am using the latest Windows 10g version.

You are correct in that the data volumes will be low, at most 1000-2000 records per day. None of the tables will be more than 600,000 records.

What is happening at the moment is that if a record exists in the database with the values:

ADMISSION_ID ADMISSION_DATE ADMISSION_SOURCE

------------------   ----------------------  --------------------------
123456                  10/10/2004                  GP

and I want to add new records to the database plus include an updated entry of:

ADMISSION_ID ADMISSION_DATE ADMISSION_SOURCE

------------------   ----------------------  --------------------------
123456                  10/10/2004                  HOSP

then because ADMISSION_ID is the primary key using APPEND results in a "ORA-00001: unique constraint (ADMISSIONS_PK) violated" error.

As far as I understand the TRUNCATE and DELETE options delete the entire table contents which is not what I want. I really only want the changed record to be updated.

Many thanks for taking the time to reply. I apologise if any of the above is unclear. I work in a small team of 3 people with limited resources and we are moving from a Microsoft environment to Oracle. I'm not afraid to read the documentation if I am given the correct area to research.

Regards,
Ian
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1094696048.932999_at_yasure...
> Ian wrote:
>
> > Hallo,
> >
> > I am having trouble with the functionality of the sqlldr utility,
> > specifically in regards to the insert, append, truncate, delete options.
> >
> > Task: Using sqlldr to update a number of tables from comma delimited
files.
> > This will happen on a daily basis. The comma delimited files contain
new
> > and updated record entries. The source data is from another transaction
> > system which does not have a odbc driver available, hence daily extracts
> > which are then ftp'd to the Oracle database server for update to the
> > database which is used for reporting.
> >
> > Problem: Using append the new records are added however any record
which is
> > already in the database is discarded due to the primary key constraint.
> > Using either truncate or delete blows away all records in the existing
> > Oracle table leaving only the records from the upload in the table and
the
> > requirement is to keep all existing records + any new and updated
records.
> >
> > Required behaviour: I would like all the records to be appended (new)
and
> > updated for those records which already exist in the database as one or
more
> > fields will have changed from the source transaction application.
> >
> > Thanks in advance for any helpful suggestions.
> >
> > Regards,
> > Ian
>
> I don't see why APPEND doesn't work for you given what you've stated.
> But it sounds to me like the amount of data is so small you should be
> thinking external tables.
>
> What version of Oracle?
>
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)
>
Received on Wed Sep 08 2004 - 22:01:37 CDT