Use of Merge Statement

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 :
Create table a_merge
 as select empno , ename
from emp;

Now the PL/SQL Block or SQL query:

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;

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *