CODEWITHSUNDEEP
oracle-databasemerge
Will1v
Will1v

Reputation: 196

Oracle: Merge equivalent of insert all?

I've tried to find an answer on several forums with no luck, so perhaps you can help me out. I've got an INSERT ALL request that inserts thousands of rows at once. 

INSERT ALL
   INTO my_table (field_x, field_y, field_z) VALUES ('value_x1', 'value_y1', 'value_z1')
   INTO my_table (field_x, field_y, field_z) VALUES ('value_x2', 'value_y2', 'value_z2')
   ...
   INTO my_table (field_x, field_y, field_z) VALUES ('value_xn', 'value_yn', 'value_zn')
SELECT * FROM DUAL;

Now I'd like to amend it to update rows when some criteria are met. For each row, I could have something like:

MERGE INTO my_table m
    USING (SELECT 'value_xi' x, 'value_yi' y, 'value_zi' z FROM DUAL) s
    ON (m.field_x = s.x and m.field_y = s.y)
    WHEN MATCHED THEN UPDATE SET 
        field_z = s.z,
    WHEN NOT MATCHED THE INSERT (field_x, field_y, field_z) 
        VALUE(s.x, s.y, s.z);

Is there a way for me to do a kind of "MERGE ALL" that would allow to have all those merge requests in one?

Or maybe I'm missing the point and there's a better way to do this?

Thanks,

Edit: One possible solution is to use "UNION ALL" for a set of selects from dual, as follows:

MERGE INTO my_table m
    USING (
        select '' as x, '' as y, '' as z from dual
        union all select 'value_x1', 'value_y1', 'value_z1' from dual
        union all select 'value_x2', 'value_y2', 'value_z2' from dual
        [...]
        union all select 'value_xn', 'value_yn', 'value_zn' from dual
    ) s
    ON (m.field_x = s.x and m.field_y = s.y)
    WHEN MATCHED THEN UPDATE SET 
        field_z = s.z,
    WHEN NOT MATCHED THEN INSERT (field_x, field_y, field_z) 
        VALUES (s.x, s.y, s.z);

NB: I've used a first empty row to be able generate all rows in the same format when I write the request. I also specify the columns names there.

Another solution would be to create a temporary table, INSERT ALL data into it, then merge with the target table and delete the temporary table.

Upvotes: 2

Views: 3329

Answers (1)

Boneist
Boneist

Reputation: 23588

If you're passing in tens of thousands of rows from your python script, I would do:

  1. Create a global temporary table (GTT - this is a permanent table that holds data at session level)
  2. Get your python script to insert the rows into the GTT
  3. Use the GTT in the Merge statement, e.g.:

merge into your_main_table tgt
  using your_gtt src
    on (<join conditions>)
when matched then
  update ...
when not matched then
  insert ...;

Upvotes: 2

Related Questions