Merge data into table in Oracle

If you need to merge some data into one table you want by replacing manual inserting with ' merge into ' operation, maybe the following code is what you want. You can use it to make u get more efficient. Pls follow steps to operate.

 

 

1.create table that you want to insert data into it or create it in the following script test.sql.

--test.sql

set serveroutput on;
spool insert.log
drop table properties;
create table properties (identifier varchar2(4000), value varchar2(4000));

@query_properties_insert_jeff.sql;
show errors;

spool off

 
2.create the below script query_properties_insert_jeff.sql, and run it to merge data like query sentences or other data into the above table

--query_properties_insert_jeff.sql

--table name: properties, this table have two cloumns, identifier and value like key-value
merge into properties p
  using (select 'getSalesCCIDfromAcctRowIdReq' identifier, 'select PARTY_SALES.SALES_CCID from CCI.PARTY_SALES where PARTY_SALES.Siebel_Row_Id = ?' value from dual) s
  on (p.identifier = s.identifier)
 when matched then update set p.value = s.value
 when not matched then insert (identifier, value) values (s.identifier, s.value);



merge into properties p
  using (select 'GCSM_TIMEOUT' identifier, 6000 value from dual) s
  on (p.identifier = s.identifier)
 when matched then update set p.value = s.value
 when not matched then insert (identifier, value) values (s.identifier, s.value);

 

转载于:https://www.cnblogs.com/Jeffrey-xu/p/4977922.html