Merge, when matched insert

Question

I wish to perform constructive merge in Oracle i.e. if a matching record is found in the source table, a new record with current timestamp should be added to the destination table.

How do I do this using merge in Oracle? Below is my code which gives a "missing keyword" error.

merge into studLoad sl
using student s
on(s.studID=sl.studID)
when matched
then
insert(sl.studID,sl.studName)
values(s.studID||'abc',s.studName)
when not matched
then
insert(sl.studID,sl.studName)
values(s.studID,s.studName);

Also, I dont know how to add current timestamp along with the studName. Any suggestions for the same would be welcome.

Answer 1

This shouldn't be done with a merge (as Justin said) but with two inserts. Just one row for each match (also with duplicate matchings as you said in the comment) and then all the rows from student. This can be done in the following way.

insert into studLoad ( studID, studName, <<timestamp column>> )
select studID, studName, systimestamp
from student
where studId in (
        selct studId
        from studLoad
    )
/
insert into studLoad (studID, studName)
select studID, studName
from student
/

And don't invert this execution order!!!