Oracle merge-statement

The merge statement has been in Oracle since version 9, but is still not very widely used.
When looking at data loading and merging code I think the most common bit of PL/SQL I've seen is the "merge" statement written in pl with slow and boring if-statements to see check if an entity exists in a table and if it exists the pl-code will do an update, otherwise it will do an insert.
Two words, super slow.

A much better solution is to use the merge-statement which does exactly that, it's basically
"if exists then update else insert". Merge data from one data entity to another.
The basic concept is sort of a join between the loading entity and the destination table.
You can load from a normal table or from select query.

Example:

SQL> select * from emp_load;

EMPNO ENAME      JOB         MGR HIREDATE    SAL     COMM     DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1450     1400         30
 7788 SCOTT      ANALYST    7566 19-APR-87  3500                  20
 7902 FORD       SALESMAN   7698 03-DEC-81  3000     1300         20
 7100 CHARLES    CLERK      7788 22-NOV-06  1150                  10
 7302 SALLY      CLERK      7902 22-NOV-06  1300                  30

SQL> MERGE INTO emp e USING emp_load l
  ON (e.empno=l.empno)
  WHEN MATCHED THEN
    UPDATE SET
      e.ename = l.ename,
      e.job = l.job,
      e.mgr = l.mgr,
      e.hiredate = l.hiredate,
      e.sal = l.sal,
      e.comm = l.comm,
      e.deptno = l.deptno
  WHEN NOT MATCHED THEN
    INSERT VALUES (
      l.empno, l.ename, l.job,
      l.mgr, l.hiredate,
      l.sal, l.comm, l.deptno
    )
/

5 rows merged.

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE    SAL     COMM     DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
 7100 CHARLES    CLERK      7788 22-NOV-06  1150                  10
 7302 SALLY      CLERK      7902 22-NOV-06  1300                  30
 7369 SMITH      CLERK      7902 17-DEC-80   800                  20
 7499 ALLEN      SALESMAN   7698 20-FEB-81  1600      300         30
 7521 WARD       SALESMAN   7698 22-FEB-81  1250      500         30
 7566 JONES      MANAGER    7839 02-APR-81  2975                  20
 7654 MARTIN     SALESMAN   7698 28-SEP-81  1450     1400         30
 7698 BLAKE      MANAGER    7839 01-MAY-81  2850                  30
 7782 CLARK      MANAGER    7839 09-JUN-81  2450                  10
 7788 SCOTT      ANALYST    7566 19-APR-87  3500                  20
 7839 KING       PRESIDENT       17-NOV-81  5000                  10
 7844 TURNER     SALESMAN   7698 08-SEP-81  1500        0         30
 7876 ADAMS      CLERK      7788 23-MAY-87  1100                  20
 7900 JAMES      CLERK      7698 03-DEC-81   950                  30
 7902 FORD       SALESMAN   7698 03-DEC-81  3000     1300         20
 7934 MILLER     CLERK      7782 23-JAN-82  1300                  10

16 rows selected.

If you want to load from a query you can use this syntax:

MERGE INTO emp e USING 
    (select * from emp_load 
      where hiredate > sysdate - 7) l
  ON (e.empno=l.empno)
...

Check out the merge docs .