Creating a procedure to insert from one table to another

[Phindile]

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;

[ErmanArslansOracleBlog]

I'm not sql expert and this is not the scope of this forum but still I can help you..

What is the problem that you encounter when you run your insert statement? Please describe it with all the details.

[ErmanArslansOracleBlog]

Okay. I saw your problem..
You are missing the "into" clause in your sql.

Ex:

insert into TABLE ....blabla

Also, you may use MERGE statement rather than this insert.. It may be more logical for your case..

[Phindile]

Thanks for your response, yes I noticed i missed the "insert into" and it still gave some errors afterwards. So now I added "commit" after the "where" clause, and now I'm getting this error:
Error(14,22):PL/SQL:ORA-00923:FROM keyword not found where expected.

Thanks

[ErmanArslansOracleBlog]

Dont put commit there.

Commit after insert.. after ";" of insert.

you need to study sql.. You have syntax errors.


Example:

insert into tableB
Select * from
(Select A.*
from tableA A
left join tableB B
on a.id = B.id
where b.id is null )A;

commit;

[Phindile]

Hi, it's fine now. I managed to get it right.

Thank you.

On Fri, Jan 12, 2018 at 12:33 PM, ErmanArslansOracleBlog [via Erman Arslan's Oracle Forum] <[hidden email]> wrote:

I'm not sql expert and this is not the scope of this forum but still I can help you..

What is the problem that you encounter when you run your insert statement? Please describe it with all the details.

---

If you reply to this email, your message will be added to the discussion below:

http://erman-arslan-s-oracle-forum.2340467.n4.nabble.com/Creating-a-procedure-to-insert-from-one-table-to-another-tp4888p4891.html

To unsubscribe from Creating a procedure to insert from one table to another, click here.
NAML