Question: Migrate records with INSERT INTO x SELECT FROM y statement and loop

Question

Migrate records with INSERT INTO x SELECT FROM y statement and loop

Answers 2
Added at 2017-01-02 16:01
Tags
Question

I need to migrate all the records from a tableA to a tableB. At the moment I'am simply using the following statement:

INSERT INTO table1 (id, name, description) SELECT id, name, descriptionOld
FROM table2;
COMMIT;

The problem is that if there is a high number of records the temporary tablespace might not have enough space to handle this statement. For this reason I would like to know if there is any way to still have this statement over a loop that commits, for example, 1000 records at the time.

Thank you!

Answers to

Migrate records with INSERT INTO x SELECT FROM y statement and loop

nr: #1 dodano: 2017-01-02 16:01

Say you have these tables:

create table sourceTab( a number, b number, c number);
create table targetTab( a number, b number, c number, d number);

and you want to copy records from sourceTab to targetTab filling both the coumns c and d of the tagret table with the value of the column C in the source. This is a way to copy the records not in a single statement, but in blocks of a given number of rows.

DECLARE
    CURSOR sourceCursor IS SELECT a, b, c, c as d FROM sourceTab;

    TYPE        tSourceTabType IS TABLE OF sourceCursor%ROWTYPE;   
    vSourceTab  tSourceTabType;

    vLimit      number := 10;   /* here you decide how many rows you insert in one shot */
BEGIN   
    OPEN sourceCursor;
    LOOP
        FETCH sourceCursor 
            BULK COLLECT INTO vSourceTab LIMIT vLimit;
        forall i in vSourceTab.first .. vSourceTab.last
            insert into targetTab values vSourceTab(i);
        commit;
        EXIT WHEN vSourceTab.COUNT < vLimit;

   END LOOP;

   CLOSE sourceCursor;
END;

If you follow this approach, you may get an error when some records, but not all, have already been copied (and committed), so you have to consider the best way to handle this case, depending on your needs.

nr: #2 dodano: 2017-01-02 18:01

For huge data processing one must have a look on context switching between SQL and PLSQL engines. An approach can be let the insert from tableA to tableB and handle the error records after the insertion is completed. You create a error tableC same as your destination table to handle the error records. So once the copying of data from tableA is completed you can have a look at the error records and directly do and insert into to tableB after making correction. See below how you can do it.

declare
    cursor C is
    select *
      from table_a;

    type array is table of c%rowtype;
    l_data array;

    dml_errors EXCEPTION;
    PRAGMA exception_init(dml_errors, -24381);

    l_errors number;  
    l_idx    number;
begin
    open c;
    loop
        --Limit 100 will give optimal number of context switching and best perfomance
        fetch c bulk collect into l_data limit 100; 
        begin
            forall i in 1 .. l_data.count SAVE EXCEPTIONS
                insert into table_b 
                values l_data(i);
        exception
            when DML_ERRORS then
                l_errors := sql%bulk_exceptions.count;
                for i in 1 .. l_errors
                loop                    
                    l_idx   := sql%bulk_exceptions(i).error_index;
                   --Insertnig error records to a error table_c so that later on these records can be handled. 
                   insert  into table_c
                    values l_data(l_idx);
                end loop;
              commit;
        end;
        exit when c%notfound;
    end loop;
    close c;
 commit;
end;
/
Source Show
◀ Wstecz