Merging data from the old table into a new one for the monthly archive

Question

I have a sql instruction to insert data into a table for archiving, but I need a monthly merge instruction to update the new table (2) with any data that was changed in the old table (1), which should now be transferred to archive.

Part of the problem is deleting the moved data from the old table. My insertion does not do this, but I need to have it where the stored data is deleted from the source table.

Is there one sql statement that moves data from one table to another this way? Or should it be a two-step operation?

the original operator moved data based on age and several other relative factors. insert:

INSERT /*+ append */ INTO tab1 SELECT * FROM tab2 WHERE (Postingdate < TO_DATE ('2001/07/01', 'yyyy/mm/dd') OR jobname IS NULL) AND STATUS <> '45'; 

All help is appreciated ...

Answer 1

The merge statement allows you to do this in one of the statements by adding a delete statement to the update clause. See Oracle documentation for federation .

Answer 2

I think you should try this with the partition table. My idea is to create a table having a range of partitions by date:

 create table(id number primary key,name varchar,J_date date ) partition by range(J_date)(PARTITION one_mnth VALUES LESS THAN(sysdate-30)), partition by range(J_date)(PARTITION one_mnth VALUES LESS THAN(maxvalue))); 

then move this section to another table and crop this section