CREATE TABLE EMP
(
  ENO    NUMBER(4),
  ENAME  VARCHAR2(4 BYTE)
)

LOAD DATA
    INFILE 'sample.dat'
    fields terminated by ','
   BADFILE 'sample.bad'   DISCARDFILE 'sample.dsc'
    APPEND
    INTO TABLE emp
    (
      ENO,
      ENAME)   
    

create table ldrtest(code varchar2(10),id varchar2(10),
                     row_name varchar2(25),COL1 NUMBER(4),
COL2 NUMBER(4),COL3 NUMBER(4),crt_tm date, md_tm date, 
crt_user varchar2(10),MOD_USR VARCHAR2(10)) 
/

CREATE UNIQUE INDEX ldrtest_UK3 ON ldrtest
(CODE, ID)


INSERT INTO LDRTEST VALUES('24','0','AGREEMENT',1,1,2,SYSDATE,TO_DATE('2008/08/17','YYYY/MM/DD'),'SCOTT','PETER')
/

INSERT INTO LDRTEST VALUES('24','0','FINAGREE',1,1,2,SYSDATE,TO_DATE('2008/09/10','YYYY/MM/DD'),'SCOTT','JOE')

/

INSERT INTO LDRTEST VALUES('74','704','AGREEMENT',1,1,2,SYSDATE,NULL,'SCOTT',NULL)

/



INSERT INTO LDRTEST VALUES('74','704','FINAGREE',1,1,2,SYSDATE,NULL,'SCOTT',NULL)
/

MERGE INTO ldrtest D
   USING (SELECT inv_code,pool_id,row_name,col1,col2,col3,crt_tm,md_tm,crt_user,mod_usr FROM stg_ldrtest) S
      ON (d.inv_code = s.inv_code and d.pool_id = s.pool_id )
   WHEN MATCHED THEN UPDATE SET  d.row_name = s.row_name,
                                d.col1 = s.col1,
                                d.col2 = s.col2,
                                d.col3 = s.col3,
                                 d.crt_tm = s.crt_tm,
                                d.md_tm = s.md_tm,
                                d.crt_user = s.crt_user,
                                d.mod_usr = s.mod_usr
        WHEN NOT MATCHED THEN INSERT (d.inv_code,d.pool_id,d.row_name,d.col1,d.col2,d.col3,d.crt_tm,d.md_tm,d.crt_user,d.mod_usr)
     VALUES (s.inv_code,s.pool_id,S.row_name, s.col1,s.col2,s.col3,s.crt_tm,s.md_tm,s.crt_user,s.mod_usr);