Loop on static content to upsert many rows in Oracle SQL

Zoboki Árpád — Thu, 13 Apr 2023 08:01:43 +0000

Sometimes you just want to add many test rows into your Oracle DB, by iterating over fixed content. If your DB already has some rows which you then need to update, then what you're looking for is the upsert (update or insert) operation.

Here's one example how to do this in Oracle SQL, without creating a temporary table:

begin
for i in (select * from table(sys.dbms_debug_vc2coll('1234','5678')))
loop
merge into my_target_table target
using (
  select i.COLUMN_VALUE as acc_no,
  'N' as static_val,
  '1' as another_static_field
  from dual
  ) temp on (target.acc_no = temp.acc_no)
when matched then
  update set target.MY_FIELD_TO_UPDATE = temp.static_val, target.SECOND_FIELD = temp.another_static_field
when not matched then
  insert values (temp.acc_no, temp.static_val, temp.another_static_field);
end loop;
end;
/

All parts of this syntax are necessary, including the ending / which signifies the end of the anonymous PL/SQL code block, so you can execute it in e.g. SQuirrel SQL.
sys.dbms_debug_vc2coll('1234','5678') this call outputs whatever list of data you need to loop over
from dual using the special dual table keyword, we don't have to create a table to hold our static data, it will be in memory only
when matched then the matched directive allows us to do the upsert operation (update when already existing, insert when new)
i.COLUMN_VALUE pay special attention to the .COLUMN_VALUE field, which is how Oracle allows us to access each single value that is being looped over

Forem: Zoboki Árpád

Loop on static content to upsert many rows in Oracle SQL