a row-level operator for copying?

From: Mark Harrison <mh_at_pixar.com>
Date: Wed, 25 Jul 2007 05:26:33 GMT
Message-ID: <daBpi.30181$C96.5951@newssvr23.news.prodigy.net>

I've got two identical tables -- one has current data, and one has historical data. I've got a nightly job which moves older data from the current table to the history table. In the code sample below, these tables are atest and atest_history).

Three questions:

1. The code works, but I don't like having to list out all of the columns in the insert statement -- is there some way to insert the data in one go, something like: insert into atest_history values atest_rec;
2. Are there any other features or extensions that make this easier? It would be great if there were some kind of statement like: move rows from table1 to table2 where ...;
3. I'm a newbie at pl/sql, feel free to suggest where the code below is lousy!

Many TIA!
Mark

---

• archive old rows from table "atest" to "atest_history" create or replace procedure ARCHIVE_ATEST as begin declare atest_rec atest%rowtype;

        cursor getfinished
        is
            select a, seq, c
              from atest
             where c = 1;
    begin
        open getfinished;

        loop
            fetch getfinished
             into atest_rec;

            exit when getfinished%notfound;
            dbms_output.PUT_LINE('v_atest = ' || atest_rec.a);

            insert into atest_history
                        (a, seq, c)
                 values (atest_rec.a, atest_rec.seq, atest_rec.c);

            delete from atest
                  where seq = atest_rec.seq;
        end loop;

        dbms_output.PUT_LINE('done,rowcount=' || getfinished%rowcount);

        close getfinished;

    end;
end ARCHIVE_ATEST; Received on Wed Jul 25 2007 - 00:26:33 CDT