Merge statment in oracle

Merge statment in oracle
========================


drop table emp1;

create table emp1 as select * from emp ;


drop table dept1;

create table dept1 as select * from dept;


MERGE INTO emp1 a
USING dept1 b
ON (a.deptno = b.deptno)
WHEN MATCHED THEN
UPDATE SET a.sal = b.deptno
WHEN NOT MATCHED THEN
INSERT (empno, sal)
VALUES (b.deptno,b.deptno);

Conditionally insert into ALL tables

Conditionally insert into ALL tables
======================================

INSERT ALL
WHEN SAL>10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN
INTO mgr_history VALUES(EMPID,MGR,SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;

Example of Oracle External Table

Example of Oracle External Table
================================

CREATE OR REPLACE DIRECTORY dir_test AS 'C:\dir_test';

GRANT READ,WRITE ON DIRECTORY dir_test TO scott;

data on text.txt
/*
00100,halim,md,1234566,halim@yahoo.com,58
00200,jamal,khan,456789,jamal@yahoo.com,50
00300,nazu,haque,456789,nazu@yahoo.com,52
00400,nazim,md,456799221,nazim@yahoo.com,90
00500,niger,sultana,123456787,niger@yahoo.com,50
00600,zakir,Dr,465465464,zakir@yahoo.com,17
*/

DROP TABLE external_table_emp;


CREATE TABLE external_table_emp
( empid VARCHAR2(6),
last_name VARCHAR2(100),
first_name VARCHAR2(100),
phone_no VARCHAR2(20),
email VARCHAR2(200),
age NUMBER(3)
)
ORGANIZATION EXTERNAL
( DEFAULT DIRECTORY dir_test
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
)
LOCATION ('test.txt')
);


SELECT * FROM external_table_emp;