Oracle loop insert

Question

I'm having issues to perform one mass update in our Oracle DB.

I would like to do mass insert to mapping table which we can call map

There are other two tables from which I have to obtain the data The first table is the account table from which I can select the desired accounts for inserting with this query:

select account_id
from account
where company_id in (
select company_id
from company
where company_name = 'string');

This query will select my all the accounts depending on the company name which I want to insert.

Next I have table which we can call group and in this table I have exact number of groups which I inserted, groups have UIDs so we have group_id 1 to 500.

Here is what i need to do: I need to insert into table map UID mappings between the accounts and groups. There is no special order in which needs to be done

insert into map (account_id, group_id) 
  values (number,number);

But unfortunately I don't know how to do such mass insert with selects which are containing more then one result and also how to count the looping.

So my idea is do something like this:

insert into map (account_id, group_id) values ((select account_id
from account
where company_id in (
select company_id
from company
where company_name = 'string')),loop from 1 to 500);

With rule that group_id can be inserted with many account_ids but one account_id can be linked only to one group.

Of course this query will not work, I'm just trying to express what I want to do. Hope this make sense.

I since I know what exactly I want to insert where and I have all the counts I know that I have 500 account_ids and 50 group_ids. Also they are in order meaning account_id 1 to 500 and group_id 1 to 500, so I think in this case it should be fairly easy to loop the insert somehow.

I hope I have made myself clear as much as possible.

Thank you for your answers and suggestions!

Answer 1

This will insert every combination of account id and group id:

INSERT INTO map(account_id, group_id)
SELECT a.id, g.id
FROM account a, group g
WHERE a.company_id IN ( select company_id 
                        from company 
                        where company_name = 'string');

EDIT: If I understood you comment correctly, you want something like this:

INSERT INTO map(account_id, group_id)
SELECT a.id, g.id
FROM account a, group g
WHERE a.company_id IN ( SELECT company_id 
                        FROM company 
                        WHERE company_name = 'string')
AND 1000 <= a.id AND a.id <= 1500
AND g.id = a.id + 1000;

Which will connect account 1000 with group 2000, account 1001 with group 2001, and so on. A bit unnecessary to join the group now, but it will make sure a row with the expected group id exists (and skip it if it doesn't).

Answer 2

Something like this will work. It uses the INSERT ... SELECT syntax (see an example here) to insert multiple rows from a query.

To loop, cross join the results with this query, which will give you a table of 500 rows with sequential numbers.

select LEVEL as group_id from dual connect by LEVEL <= 500

Here's the final answer. It's untested so you may need to fix and tweak, but that's the general approach:

insert into map (account_id, group_id)
  select account_id, group_id
    from account
    where company_id in (select company_id 
                         from company 
                         where company_name = 'string')
  cross join (select LEVEL as group_id
              from dual 
              connect by LEVEL <= 500)

Addition - OP asked how a query including groups 1,000 through 1,500 (instead of 1 through 500) would work...

The CONNECT BY LEVEL always has to start at 1, so you'll need to CONNECT BY LEVEL <= 1500 in an inner query and filter out 1 through 999 with an outer WHERE clause. Here's an example; the differences are all in the cross-joined query:

insert into map (account_id, group_id)
  select account_id, group_id
    from account
    where company_id in (select company_id 
                         from company 
                         where company_name = 'string')
  cross join (
     select group_id from (
       select LEVEL as group_id
         from dual
         connect by LEVEL <= 1500)
     where group_id >= 1000)

I don't have Oracle within reach at the moment so this is untested like my prior answer.

Also note: going up to 1,500 like this is no problem, but at some point you'll notice a slowdown. I don't know if it will be at 10,000 or 100,000 or a million, but sooner or later you could hit a wall.

Answer 3

Answer to your question:

INSERT INTO map(account_id, group_id)
SELECT a.id, g.id
  FROM account a
 INNER JOIN company c
    ON a.company_id = c.company_id
   AND c.company_name = 'string'
 CROSS JOIN group g

My pet peeve, quit controlling the SQL! You don't have to use IN.