CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Kent Anderson
Kent Anderson

Reputation: 486

Copy records with insert select, then update attributes in the same set of records

I have an Oracle procedure in which I would like to do the following:

  1. Copy records from one table to another
  2. Update original records to indicate they have been copied

Currently, I am using a FOR loop to process each record individually. This works, but it is a lot of code to do something I think could be simpler.

I would like to:

  1. Eliminate the for loop and copy the records with an INSERT INTO ... SELECT ... statement
  2. If copy is successful, update all selected records.

This may seem simple, just one INSERT and an UPDATE statement with the same WHERE clause. However, in the time between execution of the two statements, more records may be inserted that need to be copied. If I use the same WHERE clause, I may mark records as processed that have not actually been processed.

Is there a way I can save a list of the Primary Keys for use in both statements or merge the statements? Or would you recommend I stick with the FOR loop and process the records one at a time? All constructive input welcome.

Upvotes: 1

Views: 826

Answers (2)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

If you run the two statements within a transaction, any failures will cause both the insert and the update to be rolled back.

insert into tgt..
select * from src
where <condition>
  and row_status <> 'copied'; --ignoring records after they have been copied once?

update src
set row_status = 'copied'
where <same_where_condition_as_before>

commit;

If there are new rows inserted in the source table after they have been read, you might need to run the block again, with a changed where condition if appropriate.

Upvotes: 3

Jon Heller
Jon Heller

Reputation: 36807

Rajesh's answer should work well if there is a small chance of the source table being updated concurrently. If the source table is updated frequently the serializable transaction will fail too often.

Another approach would be to use flashback query to get a point-in-time look at the table. The as of syntax is a convenient way to see what a table looked like in the (recent) past.

declare
    v_current_scn number;
begin
    v_current_scn := dbms_flashback.get_system_change_number;

    insert into tgt..
    select * from src
    where <condition>
        and row_status <> 'copied'; --ignoring records after they have been copied once?

    update src
    set row_status = 'copied'
    where rowid in
        (
            select rowid
            from src as of scn v_current_scn
            where <same_where_condition_as_before>
        );
end;
/

Upvotes: 0

Related Questions