Oracle - How to bulk insert values in one query with an array of values to insert

Question

I have these tables:

A {
id,
name
}

B {
id,
name,
aId
}

aId is a foreign key to table A

So I have a list of strings that need to be inserted into table B.

List: names['name1', 'name2']

Also I have a select statement that fetches me a list of A.id's.

List: id's[1, 2]

The problem is that Table B needs to have rows inserted to it for every A.id that was queried and at the same time the string list should be taken into consideration.

End result would be that I get id's (1, 2) for instance, so 4 rows are created (2 for each id because every id needs to insert all the string in the list):

insert into b (id, name, aId) values (b_id_seq.nextval, name1, 1)
insert into b (id, name, aId) values (b_id_seq.nextval, name2, 1)

insert into b (id, name, aId) values (b_id_seq.nextval, name1, 2)
insert into b (id, name, aId) values (b_id_seq.nextval, name2, 2)

What I have gotten to work is write the select statement which returns an array of id's. I also tried to implement a select with these 2 lists but without luck.

First attempt was to use IN clause inside of specific column value part and second attempt was to use INSERT ALL. Could not figure out how to generate the insert statements dynamically in the last case tough.

How would one solve this kind of INSERT statement?

score 1 · Accepted Answer · edited May 23 '17 at 10:29

This sounds like a good candidate to use some PL/SQL. You can do dynamic queries with this... but truth be told you probably don't need to and can get away with some cursor usage.

create proc myproc(inListName in customType)
as

--this cursor would get you your list of IDs...
cursor1 is
   select id from table;

c1row cursor1%rowtype;

begin

  --this cursor opens and is effectively your "outer loop"
  open cursor1;
  LOOP
    -- Retreive one row.
    FETCH cursor1 INTO c1row;
    EXIT WHEN cursor1%NOTFOUND;
      --not exact syntax here.. but you get the idea...
      FOR name IN inListNames
      LOOP
        insert into b (id, name, aId) values (b_id_seq.nextval, name, c1row.id);
      END LOOP;          

  END LOOP;
  close cursor1;
end
/

NOTE: This is untested and just off the top of my head... but it would work with something along these lines. If you're doing this with a lot of data, I'd recommend you instead do a BULK INSERT (just google Oracle bulk insert...) and use that instead, as performance wise it works better.

NOTE2: This is using a custom datatype to PASS in a parameter that would contain your list of names. Here is a link to show you how you can set up a user defined type to do that.
Passing an array of data as an input parameter to an Oracle procedure

Alternatively, if that's too much over-kill for you or you feel it's to over-engineered, maybe you can somehow run a query that would get you those list of names? If that's the case, you could just create a second cursor to get that list of names and iterate over that.

Oracle - How to bulk insert values in one query with an array of values to insert

1 Answers1