Re: merge with INSERT... SELECT -- possible?

On Mar 21, 2:00 pm, "foothillbiker" <foothillbi..._at_gmail.com> wrote:
> All,
>
> platform: 10.2.0.2
>
> I'm trying to load data into an external table. The update portion of
> MERGE works great. But when rows are not matched, I need to find data
> from another table so I would like to use the syntax INSERT....SELECT
>
> (This is a simplication of the actual situation)
>
> I'm searching for a way to do something along the lines of
>
> MERGE INTO salesperson S USING salespersonexternaltable XTAB
> ON (S.lastname = XTAB.lastname)
> WHEN MATCHED THEN UPDATE
> SET S.salestodate = S.salestodate + XTAB.sales
> WHEN NOT MATCHED THEN
> INSERT INTO S (pk, lastname, salestodate, manager)
> SELECT salesseq.nextval, x.lastname, x.salestodate, EMP.MANAGER
> FROM salespersonexternaltable X, EMP
> WHERE EMP.LASTNAME = X.LASTNAME
> /
>
> However, all examples I've been able to find want use the syntax
> INSERT INTO table (x, y, z)
> VALUES (a, b, c)
>
> Is there a workaround, or should I just do the UPDATES w/ MERGE and
> INSERTS separately?
>
> Many thanks.
>
> REgards,
> Chas.

Maybe you could use a trigger on the insert. then using

MERGE INTO salesperson S USING salespersonexternaltable XTAB ON (S.lastname = XTAB.lastname)
WHEN MATCHED THEN UPDATE
SET S.salestodate = S.salestodate + XTAB.sales WHEN NOT MATCHED THEN
INSERT INTO S (pk, lastname, salestodate, manager) values  (XTAB.pk,XTAB.lastname, XTAB.salestodate, XTAB.manager) ;

where the trigger handles replacing what needs replacing.

   Ed Received on Wed Mar 21 2007 - 13:53:44 CDT