If no rows satisfy the condition, then Oracle inserts the row into the target table based on the corresponding MERGE source row.
If the column list after the INSERT keyword is left out, the number of columns in the target table must match the number of values in the VALUES clause.
If you wish to insert all of the MERGE source rows into the table, you should use what is known as a "constant filter predicate" in the ON clause.
The MERGE statement uses the merge_insert_clause when the condition of the ON clause is false.
As with any normal insert, if the insert clause is executed, all insert triggers defined on the target table are fired.
Since MERGE combines INSERT and UPDATE operations, you must have the INSERT and UPDATE object privileges on the target table, and of course, the SELECT object privilege on the source table.
If you need to specify the DELETE clause of the merge_update_clause, then you must also have the DELETE object privilege on the target table.
If the condition is false, the update operation is skipped when merging the row into the target table.
You can specify the DELETE where_clause to clean up data in a table while the MERGE statement is populating or updating it.
What's more, we can wrap up the whole transformation process into this one Oracle MERGE command, referencing the external table and the table function in the one command as the source for the MERGED Oracle data.
file,10) full (contracts_file) */ * from contracts_file ))) f on d.contract_id = f.contract_id when matched then update set desc = f.desc, init_val_loc_curr = f.init_val_loc_curr, init_val_adj_amt = f.init_val_adj_amt when not matched then insert values ( f.contract_id, f.desc, f.init_val_loc_curr, f.init_val_adj_amt); In Oracle Database 10g, the MERGE statement has been extended to cover a larger variety of complex and conditional data transformations, allowing faster loading of large volumes of data.
The MERGE statement is designed to combine multiple operations to reduce the complexity of mixed insert and update operations.