Incremental Update

Using Merge Join:

SQL> MERGE
INTO target_table tgt
USING source_table src
ON ( src.object_id = tgt.object_id )
WHEN MATCHED
THEN
UPDATE
SET tgt.object_name = src.object_name
, tgt.object_type = src.object_type
WHEN NOT MATCHED
THEN
INSERT ( tgt.object_id
, tgt.object_name
, tgt.object_type )
VALUES ( src.object_id
, src.object_name
, src.object_type );



Using SQL%ROWCOUNT (will give last DML updated row count):


SQL> DECLARE
i PLS_INTEGER := 0;
u PLS_INTEGER := 0;
BEGIN
FOR r IN ( SELECT * FROM source_table )
LOOP

UPDATE target_table tgt
SET tgt.object_name = r.object_name
, tgt.object_type = r.object_type
WHERE tgt.object_id = r.object_id;

u := u + SQL%ROWCOUNT;

IF SQL%ROWCOUNT = 0 THEN
INSERT INTO target_table
( object_id, object_name, object_type )
VALUES
( r.object_id, r.object_name, r.object_type );
i := i + 1;
END IF;

END LOOP;
DBMS_OUTPUT.PUT_LINE( u || ' rows updated.' );
DBMS_OUTPUT.PUT_LINE( i || ' rows inserted.' );
END;