Re: Sqlldr Query

"Ian" <ian_stephenson(@)hotmail.com> wrote in message news:<413fc7f7_at_derwent.nt.tas.gov.au>...
> 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)
> >

It would appear you need to consider loading a temporary table you can truncatek, then running a MERGE INTO query to:

1. Insert records that do not exist in your production table
2. Update existing records with current values

SQL*Loader is not intended for processing updates to existing records.  Hence, your reloadabble temp table and MERGE INTO query.

An examle from the documentation on MERGE INTO:

MERGE INTO bonuses D

   USING (SELECT employee_id, salary, department_id FROM employees    WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01    WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)    VALUES (S.employee_id, S.salary*0.1);

It is, I believe, the most efficient and elegant solution to your problem.

David Fitzjarrell Received on Thu Sep 09 2004 - 08:25:26 CDT