Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news.tele.dk!news.tele.dk!small.news.tele.dk!lon-transit.news.telstra.net!news.telstra.net!lon-spool.news.telstra.net!203.14.2.27.MISMATCH!reader.news.telstra.net!not-for-mail From: "Ian" Newsgroups: comp.databases.oracle.server References: <413fb8c1@derwent.nt.tas.gov.au> <1094696048.932999@yasure> <413fc7f7@derwent.nt.tas.gov.au> <9711ade0.0409090525.62e81fb4@posting.google.com> Subject: Re: Sqlldr Query Date: Fri, 10 Sep 2004 08:19:01 +1000 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Original-NNTP-Posting-Host: pc22109.dhhs.tas.gov.au Message-ID: <4140d73b@derwent.nt.tas.gov.au> Lines: 138 NNTP-Posting-Host: 202.7.15.16 X-Trace: 1094768446 lon-reader.news.telstra.net 83114 202.7.15.16 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:224626 David, Many thanks for taking time out to answer my query and especially for the code example. Cheers, Ian "David Fitzjarrell" wrote in message news:9711ade0.0409090525.62e81fb4@posting.google.com... > "Ian" wrote in message news:<413fc7f7@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" wrote in message > > news:1094696048.932999@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@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