What would be Replace command behavior(in SQL* Loader) when no data available to load?

Question

I am loading data into one temp table from that table to new table. But I did not apply the check that data is available in the source or not ? So what should be the behavior of Replace option in that case? also explain the behavior.

Answer 1

Your best bet is to look at the Oracle MERGE option. You can specify on what conditions to INSERT/DELETE/UPDATE:

MERGE INTO target_table tt
USING stage_table st
ON (st.key = tt.key)
WHEN MATCHED THEN 
    UPDATE SET tt.colA = st.colA
    DELETE WHERE (st.delInd = 'Y')
WHEN NOT MATCHED THEN INSERT (tt.key, tt.colA)
    VALUES (st.key, st.colA);