PL SQL batch processing with insert from select

Question

I have to move the data from table A to table B (they have almost the same fields).

What I have now is a cursor, that iterates over the records that has to be moved, insert one record in the destination table and updates the is_processed field in the source table. Something like:

BEGIN
    FOR i IN (SELECT *
                FROM A 
               WHERE A.IS_PROCESSED = 'N')
    LOOP
        INSERT INTO B(...) VALUES(i....);
        UPDATE A SET IS_PROCESSED = 'Y' WHERE A.ID = i.ID;
        COMMIT;
    END LOOP;

END;

The questions is, how to do the same using INSERT FROM SELECT(without the loop) and then update IS_PROCESSED of all the moved rows?

I think you could everything in one step in pure SQL using a MERGE statement. No need of PL/SQL, row-by-row is slow-by-slow. — Lalit Kumar B
– Lalit Kumar B, Commented Dec 24, 2015 at 12:53

hinotf · Accepted Answer · 2015-12-05 10:16:14Z

2

There is no BULK COLLECT INTO for INSERT .. SELECT

May be you can try this. I don't think it's better than your LOOP.

DECLARE
  TYPE        l_src_tp IS TABLE OF t_source%ROWTYPE;
  l_src_rows  l_src_tp;
BEGIN
  SELECT *
    BULK COLLECT INTO l_src_rows
    FROM t_source;
  FORALL c IN l_src_rows.first .. l_src_rows.last 
    INSERT INTO t_dest (td_id, td_value)
         VALUES (l_src_rows(c).ts_id, l_src_rows(c).ts_value);   
  FORALL c IN l_src_rows.first .. l_src_rows.last          
    UPDATE t_source 
       SET ts_is_proccesed = 'Y'
     WHERE ts_id = l_src_rows(c).ts_id;
END;

answered Dec 5, 2015 at 10:16

hinotf

1,1481 gold badge12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lalit Kumar B Over a year ago

A cursor for loop internally does a BULK COLLECT LIMIT 100

Husqvik · Accepted Answer · 2015-12-05 17:01:34Z

2

If you reverse the order and first make update and then insert you can use:

DECLARE
    ids sys.odcinumberlist;
BEGIN
    UPDATE a SET is_processed = 'Y' WHERE is_processed = 'N' RETURNING id BULK COLLECT INTO ids;
    INSERT INTO b (id) SELECT column_value id FROM TABLE(ids);
    COMMIT;
END;

In the SELECT you can join the ids table and get other data from other tables you want to insert into b.

edited Dec 5, 2015 at 17:01

answered Dec 5, 2015 at 16:52

Husqvik

5,8191 gold badge23 silver badges32 bronze badges

Comments

Shahzad Barkati · Accepted Answer · 2015-12-07 10:44:07Z

0

Hello I should prefer pure SQL rather than PLSQL. I don't know why another update statement is requires for this simpler task. Let me know if this helps.

INSERT INTO <new table>
SELECT col1,col2,
.....,'Y'
FROM <old_table>
WHERE processed_in = 'N';

edited Dec 7, 2015 at 10:44

Shahzad Barkati

2,5506 gold badges28 silver badges34 bronze badges

answered Dec 5, 2015 at 13:24

Avrajit Roy

3,3131 gold badge15 silver badges25 bronze badges

Collectives™ on Stack Overflow

PL SQL batch processing with insert from select

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related