Move rows between tables using single statement in Oracle

Question

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?

No way to do this with a single statement in Oracle. Unless we have a magician here. I would like to be wrong because that would be very handy. And btw the same question was already asked on [DBA Stack](http://dba.stackexchange.com/questions/107975/cut-records-from-one-table-into-another-in-one-oracle-sql-statement) — Thomas G, Nov 03 '16 at 09:11
If I understand well, you need a returning in the first statement, and then want to use the result of the first query in the second one; if so, have a look at [this](http://stackoverflow.com/questions/5325033/plsql-insert-into-with-subquery-and-returning-clause-oracle). — Aleksej, Nov 03 '16 at 09:12
isnt this the perfect case for a trigger? everytime you delete something, save it into the archive table? — jogoe, Nov 03 '16 at 09:13

score 7 · Accepted Answer · answered Nov 03 '16 at 09:15

7

In case your statement runs around midnight and may take longer than 1 second you should better do this:

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

delete from dbo.mytable 
 where date < theDate ;
commit;
end;
/

answered Nov 03 '16 at 09:15

Wernfried Domscheit

54,457
9
76
110

Isn't it exacly the same answer as mine? – Kacper Nov 03 '16 at 09:18
1

No, if you run your procedure at 23:59:00 and INSERT takes longer than 1 Minute then DELETE will delete different data because `trunc(sysdate) - 14` returns different value. – Wernfried Domscheit Nov 03 '16 at 09:46
OK, you're right I should set date on begining of procedure not dinamically. Thanks. – Kacper Nov 03 '16 at 10:11
Is it possible for two parallel executions of `move_to_arch` to cause rows to end up double-inserted into the archive table? The reasoning being that the subselect in the `insert` does not take any locks, so both racing transactions can see the same set of rows in the subselect. – caf Feb 22 '19 at 04:15
Yes, it would be possible to have data double-inserted into mytable_archive. Would this be a problem? One possible solution could be a partitioned table (one partition per day) where you run "exchange partition". – Wernfried Domscheit Feb 22 '19 at 18:27
I believe another solution would be to add `lock table dbo.mytable in share row exclusive mode;` prior to the insert. – caf Feb 25 '19 at 23:42

Kacper · Answer 2 · 2016-11-03T10:14:18.157

2

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();

edited Nov 03 '16 at 10:14

answered Nov 03 '16 at 09:12

Kacper

4,798
2
19
34

@NicholasKrasnov yes you're right. I'll edit answer now – Kacper Nov 03 '16 at 10:14

Isukthar · Answer 3 · 2016-11-08T13:08:25.973

0

You still need 2 statements, one for insert and one for delete. Returning will just help you saving id for exemple. Oracle provides returning into keyworks

edited Nov 08 '16 at 13:08

answered Nov 03 '16 at 09:09

Isukthar

185
8

Not sure about Postgres, but MS SQL's `OUTPUT` can be used for that - you can do `DELETE`, output the deleted rows through `OUTPUT` as a resultset and insert that resultset using `INSERT`. – Jiri Tousek Nov 08 '16 at 11:50

score 0 · Answer 4 · answered Apr 08 '21 at 06:12

Use in-memory table to cache the result of the delete statement. This avoids reading the records two times from the disk.

create or replace procedure move_to_arch as
  TYPE mytableType IS table OF dbo.mytable%ROWTYPE;
  t mytableType;
begin
  delete from dbo.mytable 
  where date < trunc(sysdate) - 14
  RETURNING col1,col2 BULK COLLECT INTO t;

  FORALL J IN t.FIRST..t.LAST
    INSERT INTO dbo.mytable_archive VALUES t(J);
end;
/

Move rows between tables using single statement in Oracle

4 Answers4