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?

Answer 1

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;

Answer 2

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;