Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

create procedure upsert(existing table name, incoming table name)

Jason_SMay 15 2015 — edited May 20 2015

Is it possible to create a procedure that does a merge (upsert) having only two parameters:

  1. Existing Table Name - name of table that will have data merged into it.
  2. Incoming Table Name - name of the table that has updated/new data.

The methodology would be:

  1. Uses the primary key(s) on the "existing table" as the linking condition
  2. Does and update if exists and insert if new
  3. no logging
  4. no hints
  5. 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))

This post has been answered by Etbin on May 15 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 17 2015
Added on May 15 2015
16 comments
560 views