INSERT using LOOP with SELECT

Question

I am trying to insert the data from other table using INSERT/SELECT combo. I also need to insert increment with specific calculation. However, I can't figure out why it is not working.

I have the table (temp_business_area) like this:

 ----------
 | bname  |
 ----------
 | London |
 | Sydney |
 | Kiev   |
 ----------

I would like to have this in enum table:

 -----------------------------------------------------------------
 | identifier    | language_id |   code | data   | company_limit |
 ----------------------------------------------------------------|
 | BUSINESS_UNIT |    0        |   100  | London | 126           |
 | BUSINESS_UNIT |    0        |   200  | Sydney | 126           |
 | BUSINESS_UNIT |    0        |   300  | Kiev   | 126           |
 -----------------------------------------------------------------

But what I get is this:

-----------------------------------------------------------------
 | identifier    | language_id |   code | data   | company_limit |
 ----------------------------------------------------------------|
 | BUSINESS_UNIT |    0        |   100  | London | 126           |
 | BUSINESS_UNIT |    0        |   100  | Sydney | 126           |
 | BUSINESS_UNIT |    0        |   100  | Kiev   | 126           |
 | BUSINESS_UNIT |    0        |   200  | London | 126           |
 | BUSINESS_UNIT |    0        |   200  | Sydney | 126           |
 | BUSINESS_UNIT |    0        |   200  | Kiev   | 126           |
 | BUSINESS_UNIT |    0        |   300  | London | 126           |
 | BUSINESS_UNIT |    0        |   300  | Sydney | 126           |
 | BUSINESS_UNIT |    0        |   300  | Kiev   | 126           |
 -----------------------------------------------------------------

And here is my loop.

BEGIN 
    FOR x IN 1 .. 3 LOOP
         INSERT INTO enum (identifier, language_id, code, data, company_limit)
         SELECT 'BUSINESS_UNIT', 0, x*100, bname, 126 FROM temp_business_area;
    END LOOP;
END;

I can't figure out where am I making mistake. Help?

You have a loop with three iterations and for each iteration select all rows from a table with three rows. That makes 9 rows in total (3 x 3). — Codo
– Codo, Commented Dec 18, 2012 at 13:43
I know in MySQL you can use limit 20,10 to get the specific row not sure about oracle so looking how to implement limit in oracle might be a hint SELECT 'BUSINESS_UNIT', 0, x*100, bname, 126 FROM temp_business_area limit x, 1 — Nerdroid
– Nerdroid, Commented Dec 18, 2012 at 13:54

score 13 · Accepted Answer · 2012-12-19 12:30:32Z

13

You are doing three inserts for each row in temp_business_area, that's why you wind up with 9 rows.

From your description of what you want to achieve you don't need the loop at all.

Just use a single insert:

INSERT INTO enum (identifier, language_id, code, data, company_limit)
SELECT 'BUSINESS_UNIT', 
        0,  
        row_number() over (order by null) * 100, 
        bname, 126 
FROM temp_business_area;

The SELECT statement will return 3 rows, and each row will be inserted into the enum table. The row_number() function will return an incrementing value for each row (1,2,3) which multplied by 100 will yield the code that you want.

Edit
(after David's comments):

The use of the windowing function does add a bit of an overhead to the statement. If the additional control over the numbering is not needed, using ROWNUM instead will be a bit more efficient (although it won't matter for only three rows).

INSERT INTO enum (identifier, language_id, code, data, company_limit)
SELECT 'BUSINESS_UNIT', 
        0,  
        rownum * 100, 
        bname, 126 
FROM temp_business_area;

edited Dec 19, 2012 at 12:30

answered Dec 18, 2012 at 13:43

user330315

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

3 Comments

David Aldridge Over a year ago

You might just use rownum instead of the row_number() over (), I think. Better for very large data sets.

David Aldridge Over a year ago

I believe that you'd avoid a window nosort operation, and be less prone to syntax errors like omitting the order by clause in the windowing function (ahem) :)

David Aldridge Over a year ago

I'm not having any luck reading explain plan ... dbms_xplan outputs through sql fiddle ... no idea why not, but it looks like it's running explain plan in the background before running queries, so it's showing the pickler fetch plan for the table() function blah blah. Anyhoo, it does show me a window nosort operation when i run a straightforward select with the analytic function and then select "View explain plan". Mystery?

Andrey Khmelev · Accepted Answer · 2012-12-19 10:01:47Z

8

you may to use two variants else:

declare

i integer := 1;

BEGIN 

    FOR x IN (select distinct bname from temp_business_area) LOOP

         INSERT INTO enum (identifier, language_id, code, data, company_limit)
         SELECT 'BUSINESS_UNIT', 0, i*100, x.bname, 126 FROM temp_business_area;

         i := i + 1;

    END LOOP;
END;

variant 2

BEGIN 

        FOR x IN 1..3 LOOP

             INSERT INTO enum (identifier, language_id, code, data, company_limit)
             SELECT distinct 'BUSINESS_UNIT', 0, x*100, bname, 126 FROM temp_business_area WHERE rownum = x;


        END LOOP;
    END;

edited Dec 19, 2012 at 10:01

answered Dec 19, 2012 at 9:53

Andrey Khmelev

1,1718 silver badges13 bronze badges

Collectives™ on Stack Overflow

INSERT using LOOP with SELECT

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related