Is it possible to create a procedure that does a merge (upsert) having only two parameters:
- Existing Table Name - name of table that will have data merged into it.
- Incoming Table Name - name of the table that has updated/new data.
The methodology would be:
- Uses the primary key(s) on the "existing table" as the linking condition
- Does and update if exists and insert if new
- no logging
- no hints
- no delete clause
Oracle 11g
MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];
<table_name> - taken from input parameter
<table_view_or_query> - taken from input parameter
<condition> - links primary key (how do we look up a single/multiple primary keys and return the list?)
<update_clause> - update all columns (other than the PK, how to get and use a dynamic list of columns)
<where_clause> - not used
<insert_clause> - insert new records for all columns (including the PK(s))