Re: a row-level operator for copying?

On Jul 25, 3:45 pm, "Chris L." <diver..._at_uol.com.ar> wrote:
> On Jul 25, 9:49 am, "Chris L." <diver..._at_uol.com.ar> wrote:
>
>
>
>
>
> > On Jul 25, 2:26 am, Mark Harrison <m..._at_pixar.com> wrote:
>
> > > 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;
>
> > If you can do without the first dbms_output.PUT_LINE then you'd have
> > to run these two commands:
>
> > insert into atest_history
> > select * from atest
> > where c = 1;
>
> > delete from atest
> > where c = 1;
>
> > You can have the total number of rows inserted/deleted by using
> > implicit cursor variables.
>
> > If you gotta have the first PUT_LINE you can use the loop for that,
> > but you should move the actual insert/delete after the loop. ie. loop
> > to PUT_LINE, insert all, delete all. Otherwise the loop is
> > unnecessary.
>
> > Regards
> > C.- Hide quoted text -
>
> > - Show quoted text -
>
> I must add that I assumed that you're purging the table in a time
> window where there's no activity on table atest, otherwise someone
> could be modifying the table (adding records with c=1 or updating them
> to c=1) and the delete statement could theoretically wipe records that
> weren't there when the insert statement ran.
>
> I don't know what the "correct" way would be to circumvent this
> problem (I'd somehow mark the rows to be moved/deleted and then move/
> delete based on that mark but that'd require an extra column)
>
> Regards- Hide quoted text -
>
> - Show quoted text -

One could BULK COLLECT the IDs of the affected columns in a collection, and BULK INSERT them into the history table and BULK DELETE them from the original table.
Or one would list partition the table, and just truncate the partition.

--
Sybrand Bakker
Senior Oracle DBA