Use of Merge Statement

July 25, 2017Posted inORACLE, SQL

Sometimes whenever we want to insert records into table with the conditions For existing records (if found then) Update, For Non Existing records (If not found) then insert

Step 1 :
Copy Code
CREATE table a_merge
 as select empno , ename
from emp;

Now the PL/SQL Block or SQL query:

Copy Code
DECLARE
   l_start   NUMBER;
BEGIN
   l_start := DBMS_UTILITY.get_time;

   MERGE INTO a_merge e
        USING (SELECT empno AS id, ename AS ename FROM emp) h
           ON (e.empno = h.id)
   WHEN MATCHED
   THEN
      UPDATE SET e.ename = h.ename
   WHEN NOT MATCHED
   THEN
      INSERT     (empno, ename)
          VALUES (h.id, h.ename);

   DBMS_OUTPUT.put_line (DBMS_UTILITY.get_time - l_start || '  hsecs');
END;
Tags: