hirolau hirolau - 1 month ago 5
SQL Question

Move rows between tables using single statement in Oracle

Due to stupid legacy system limitations I am trying to write the following query using one single statement:

insert into dbo.mytable_archive
select *
from dbo.mytable
where date < trunc(sysdate) - 14;

delete from dbo.mytable
where date < trunc(sysdate) - 14;


Using the power of Google I find that this seems possible in many other databases using the
RETURNING
clause i Postgres or
OUTPUT
clause in SQLServer but I am unable to find an equivalent solution for Oracle (V12).

Any idea for a workaround?

Answer

What are your limitations? If you want to call single statement you can:

create or replace procedure move_to_arch as
begin
insert into dbo.mytable_archive 
  select * 
    from dbo.mytable 
   where date < trunc(sysdate) - 14;

delete from dbo.mytable 
 where date < trunc(sysdate) - 14;
commit;
end;
/

And then with one statement:

exec move_to_arch();

If you want select you can convert procedure to function and do select from dual:

create or replace function f_move return number
as
begin
move_to_arch();
return 0;
end;
/

And call it by:

select f_move() from dual;
Comments