Re: newbie: copy records from one table to another

Hello!

Reiner <slugu_at_yahoo.com> ñîîáùèë â íîâîñòÿõ ñëåäóþùåå:922e593d.0107232303.6fd76d1c_at_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';

You may insert by more discrete portion, if can't increase rollback segment. May be you have large rollback segment. If it's true -> before insert type: set transaction user rollback segment LARGE;

>
> 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
Here type all columns, i.e :
values(txn.field1,txn.fileld2,...txn.filedn)

Best regards!
Yaroslav.

> delete from TRANSACTION where transaction_id = txn.transaction_id;
> END LOOP;
>
> CLOSE c1;
> END;
Received on Tue Jul 24 2001 - 02:19:34 CDT