Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: "insert or update" in one step?

Re: "insert or update" in one step?

From: Mark Townsend <markbtownsend_at_home.com>
Date: Sat, 02 Jun 2001 17:46:14 GMT
Message-ID: <B73E7476.CA07%markbtownsend@home.com>

in article NX0S6.543$Ia3.355425_at_nnrp2.sbc.net, Spencer at spencerp_at_swbell.net wrote on 6/2/01 12:54 AM:

> actually, UPSERT is not really a Teradata statement, but it
> is a descriptive name for the functionality available as part
> of the Teradata MLOAD utility.  the control card file has to
> contain two separate SQL statements: an insert statement
> and an update statement, and then a single step can refer
> to an input file, and both of the statements.  sweet!
> 
> it would be so nice to have this same functionality in the
> Oracle SQL*Loader utility.  for a datamart/warehouse
> using Oracle 7.3, we prepared a file, ran sqlldr, got the
> discard file (duplicate key values),  and used that as an
> input file to a Pro*C program to perform updates.
> 
> i wonder if 9i is going to include this functionality in the
> SQL*Loader.  now that would be Sweet...!


Sort of - instead of loading your file through SQL*Loader, in Oracle9i you just attach the input file as an external table, and then MERGE from this table into your target table.

See
http://otn.oracle.com/docs/products/oracle9i/doc_library/901_doc/server.901/ a90237/transfor.htm#13134 for a description of External Tables, and a few pages down for MERGE examples  

> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:991313547.19776.0.nnrp-12.9e984b29_at_news.demon.co.uk...

>>
>> Not necessarily a joke, UPSERT has been a valid
>> command in one of the major databases (Teradata
>> I think) for several years, and the question: 'I want to
>> insert a row, or update it if it exists, what's the
>> efficient method ?' is a prime candidate for any
>> Oracle FAQ. (There was yet another thread started
>> on it in the last few days).
>>
>>
>> --
>> Jonathan Lewis
>> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>>
>> Practical Oracle 8i: Building Efficient Databases
>> Publishers: Addison-Wesley
>>
>> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>>
>>
>>
>> Patrick Sinke wrote in message <991311196.576145_at_sleeper.capgemini.nl>...
>>>> In Orace9i you will have the command 'UPSERT' at your disposal.
>>>> 
>>> 
>>> this must be a joke!
>>> Maybe there will be a combined update/delete statement? Like uplete or
>>> delate? ;)
>>> 
>>> Regards,
>>> Patrick.
>>> 
>>> 
>>> 

>>
>>
>>

>
> Received on Sat Jun 02 2001 - 12:46:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US