merge with INSERT... SELECT -- possible?

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. Received on Wed Mar 21 2007 - 13:00:09 CDT