Insert or update table

Question

I have a list of 100k ids in a file. I want to iterate through these ids:

for each id, check if id is in a table:

• If it is, update its updated_date flag
• If not, add a new record (id, updated_date)

I have researched and found MERGE clause. The downside is, MERGE requires the ids to be in a table. I am only allowed to create a temporary table if necessary.

Can anyne point me in the right direction? It must be a script that I can run on my database, not in code.

merge into MyTable x
using ('111', '222', all my ids) b
  on (x.id = b.id)
when not matched then 
  insert (id, updated_date) values (b.id, sysdate)
when matched then
  update set x.updated_date = sysdate;

EDIT: I am now able to use a temporary table if it's my only option.

Answer 1

Given that you say you can't create a temporary table, one way might be to convert your list of ids into a set of union all'd selects, eg:

123,
234,
...
999

becomes

select 123 id from dual union all
select 234 id from dual union all
...
select 999 id from dual

You could then use that in your merge statement:

merge into MyTable x
using (select 123 id from dual union all
       select 234 id from dual union all
       ...
       select 999 id from dual) b
on (x.id = b.id)
when not matched then insert (id, updated_date) values (b.id, sysdate)
when matched then update set x.updated_date = sysdate;

If you've really got 100k ids, it might take a while to parse the statement, however! You might want to split up the queries and have several merge statements.

One other thought - is there not an existing GTT that you could "borrow" to store your data?

Answer 2

If you have access to the file from your Oracle server then you can define an external table, which will allow you to read from the file using SQL.

The syntax is based on SQL*Loader, and it's maybe not something you'd want to do for a casual job, more of a recurring task.

Alternatively you could use SQL*Loader itself to load it into a table, or even ODBC from a Microsoft Access or similar database.

Another option is to run 100,000 inserts. You can make this perform much better by taking each 100 or so inserts and wrapping them in an anonymous block, which saves roundtrips to the database, so instead of:

insert into tmp values(1);
insert into tmp values(12);
insert into tmp values(145);
insert into tmp values(234);
insert into tmp values(245);
insert into tmp values(345);
....
insert into tmp values(112425);

use ...

begin
  insert into tmp values(1);
  insert into tmp values(12);
  insert into tmp values(145);
  insert into tmp values(234);
  ...
  insert into tmp values(245);
end;
/
begin
  insert into tmp values(345);
  ...
  insert into tmp values(112425);
end;
/

If it was a regular task I'd definitely go for an external table though.