MERGE command in oracle

Ij
Posted by Ij under Oracle category on | Points: 40 | Views : 331
Merge command is used to join the data from two tables into single table.
This support for the excecution of UPDATE,INSERT and delete commands simultaniously.

first we choose emp table..
SQL>select * from emp;

create table emp_tgt
next we create empty table from source table(emp table) to target table(emp_tgt)
as
select empno,ename,job,mgr,sal from emp where 1=2;

SQL>select * from emp_tgt;

Using MERGE command copy the data emp to emp_tgt
merge into emp_tgt tgt
using emp src
on(tgt.empno=src.empno)
when matched then
update set tgt.ename=src.ename,
tgt.job=src.job,
tgt.mgr=src.mgr,
tgt.sal=src.sal
when not matched then
insert(tgt.empno,tgt.ename,tgt.job,tgt.mgr,tgt.sal)
values(src.empno,src.ename,src.job,src.mgr,src.sal);

SQL>select * from emp_tgt;

select * from emp_tgt;
OUTPUT
EMPNO    ENAME   JOB           MGR       SAL

7844 TURNER SALESMAN 7698 1500
7839 KING PRESIDENT 5000
7782 CLARK MANAGER 7839 2450
7521 WARD SALESMAN 7698 1250
7654 MARTIN SALESMAN 7698 1250
7788 SCOTT ANALYST 7566 3000
7698 BLAKE MANAGER 7839 2850
7566 JONES MANAGER 7839 2975
7499 ALLEN SALESMAN 7698 1600
7934 MILLER CLERK 7782 1300
7902 FORD ANALYST 7566 3000
7369 SMITH CLERK 7902 800
7876 ADAMS CLERK 7788 1100
7900 JAMES CLERK 7698 950

Comments or Responses

Login to post response