Re: Sqlldr Query

David,

Many thanks for taking time out to answer my query and especially for the code example.

Cheers,
Ian

"David Fitzjarrell" <fitzjarrell_at_cox.net> wrote in message news:9711ade0.0409090525.62e81fb4_at_posting.google.com... > "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