Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> newbie: copy records from one table to another

newbie: copy records from one table to another

From: Reiner <slugu_at_yahoo.com>
Date: 24 Jul 2001 00:03:08 -0700
Message-ID: <922e593d.0107232303.6fd76d1c@posting.google.com>

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;

END; Received on Tue Jul 24 2001 - 02:03:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US