I am not sure if i can archive this with a merge, because sometime i read that you can update AND insert and sometimes i read, that you can update OR insert.

MyCode:

MERGE INTO TARGETTABLE USING TEMPTABLE ON
(
TEMPTABLE.ID=TARGETTABLE.ID
TEMPTABLE.NR=TARGETTABLE.NR
TEMPTABLE.DESCRIPTION=TARGETTABLE.DESCRIPTION
)
WHEN MATCHED THEN UPDATE
SET TARGETTABLE.DATE_STOP = current date
WHEN NOT MATCHED THEN INSERT
(
TARGETTABLE.ID,
TARGETTABLE.NR,
TARGETTABLE.DESCRIPTION,
TARGETTABLE.DATE_RUN,
TARGETTABLE.DATE_STOP
)
VALUES
(
TEMPTABLE.ID,
TEMPTABLE.NR,
TEMPTABLE.DESCRIPTION,
current date,
'2999-12-31'
)

With that code provided i get:

I have tried some other update/insert statements too but i cant get to the point, to close the old line and open the new one up…