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

Preview

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;

You might Like


comments powered by Disqus