Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: "insert or update" in one step?
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...
>>>> 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. >>> >>> >>>