Creating a procedure to insert from one table to another

Posted by Phindile on Jan 12, 2018; 7:33am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Creating-a-procedure-to-insert-from-one-table-to-another-tp4888.html

HI Erman,

Could you please assist me. I have two tables, its HR_USER.HR_PERSONS and HRPERSAL_USER.PER_DATA_GEN_DET tables. What I'm trying to achieve is when I import data into the HRPERSAL_USER.PER_DATA_GEN_DET table I want it to insert only new records into the EMPLOYEECODE column in the HR_USER.HR_PERSONS table and not to insert records that already exist (to avoid duplicates).

Please have a look at my code and see where I went wrong, thanks:

 create or replace procedure HR_PERSONS_INSERT as
begin
      insert HR_USER.HR_PERSONS (EMPLOYEECODE, LASTNAME, FIRSTNAME, TITLE, INITIALS, GENDER, DATEOFBIRTH, NATIONALITYID, RSAIDENTITYNUM, RACE, MARITALSTATUS, STARTDATE, TERMINATIONPLANNED, TERMINATIONREASON)
      select M.EMPLOYEECODE,
               M.LASTNAME,
               M.FIRSTNAME,
               M.TITLE,
               M.INITIALS,
               M.GENDER,
               M.DATEOFBIRTH,
               NATIONALITYID = M.NATIONALITYID,
               M.RSAIDENTITYNUM,
               M.RACE,
               M.MARITALSTATUS,
               STARTDATE = M.DATE_OF_APPOINT,
               TERMINATIONPLANNED = M.RESIGNATION_DATE,
               TERMINATIONREASON = M.RESIGNATION_REASON
      from HRPERSAL_USER.PER_DATA_GEN_DET M
      left join HR_USER.HR_PERSONS S on S.EMPLOYEECODE = M.EMPLOYEECODE
      where S.EMPLOYEECODE is null
end;