MERGE Statment insert into from select pl sql-CodePudding

How to create merge statment that insert into table from onther select statment

my example is :

    MERGE INTO employees t
        USING (SELECT :dept_id as dept_id FROM dual ) d
        ON (t.dept_id = d.dept_id )   
      WHEN NOT MATCHED THEN
        INSERT INTO employees (
            ename,
            fname )
        SELECT
            ename,
            fname
        FROM
            trans_emps
        where trans_id = :trans_id;

CodePudding user response：

merge doesn't support such a syntax; insert clause can contain only values keyword (and list of values you're inserting into columns).

Switch to insert only (as your merge doesn't contain when matched clause anyway, so you aren't updating any rows in employees):

insert into employees (ename, fname)
select te.ename, 
       te.fname
from trans_emps te
where te.trans_id = :trans_id
  and exists (select null
              from employees a
              where a.dept_id = :dept_id
             );

CodePudding user response：

You can use INSERT INTO with a NOT EXISTS filter:

INSERT INTO employees (ename, fname)
SELECT ename, fname
FROM   trans_emps
WHERE  trans_id = :trans_id
AND    NOT EXISTS( SELECT 1 FROM employees WHERE dept_id = :dept_id)

Or you can MERGE and move the SELECT .. FROM trans_emps into the USING clause:

MERGE INTO employees dst
USING (
  SELECT ename, fname
  FROM   trans_emps
  WHERE  trans_id = :trans_id
) src
ON (dst.dept_id = :dept_id)
WHEN NOT MATCHED THEN
  INSERT ( ename, fname )
  VALUES ( src.ename, src.fname );