Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> newbie: copy records from one table to another
I'm fairly innexperienced with SQL but haven't been able to solve this relativley easy problem. I have done a search but could not find a solution.
I want to copy (move) old records from one table to a backup table.
I used:
INSERT INTO TRANSACTIONBACKUP SELECT * FROM TRANSACTION WHERE
TRANSACTION_DATE<'01-jan-2001 00:00';
But this results in me running out of rollback space so I tried going back a lot earlier but it still happens so I don't want to increase my rollbacks massively.
I then tried writng a PL/SQL script but am having problems. How do I insert record-by-record. I also just realized I need a commit after each loop step.
DECLARE CURSOR c1 IS
select * from TRANSACTION where TRANSACTION_DATE<'01-feb-00 00:00' ORDER BY transaction_date;
BEGIN
OPEN c1;
FOR txn IN c1 LOOP insert into TRANSACTIONBACKUP values (txn.*); -- help needed here delete from TRANSACTION where transaction_id = txn.transaction_id; END LOOP; CLOSE c1;
![]() |
![]() |