How to insert multiple rows using PL/SQL select

Question

so what I want is to optimize the process of inserting many rows into one table.

I have two select statements -

 one generates 800 rows (x) 
 another one 150 rows (y).

I want to INSERT into table (x, y) values so that for every x there will be all the 150 rows from x2 meaning there should be 120 000 entries in total.

Meaning:

x1-y1
x1-y2
x1-...
x1-y150
..
x800-y1
x800-y2
...
x800-y150

I can't make head or tail of it and I'm pretty sure it is really simple.

Please show us some actual sample table data. This will help you get an answer faster. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 28, 2018 at 10:26
Table 1 stores resources. Table 2 is used to define costs between them. Now first select statement lists me 150 specific resources (varchar) and second select statement 800 specific resources (varchar). For every resource from second select statement I want to have 150 entries from the first select. Table 2 with costs should look like this: row1: X1, Y1; row2: X1,Y2, row150, X1,Y150; row151: X2, Y1, row152: X2, Y2 and so on — mr_creeppl
– mr_creeppl, Commented Aug 28, 2018 at 10:38

Aleksej · Accepted Answer · 2018-08-28 10:34:16Z

Say this is the table you want to insert in:

SQL> create table yourTable (x varchar2(10), y varchar2(10));

Table created.

and you already have two queries giving some results:

SQL> select 'x_' || level as x
  2  from dual
  3  connect by level <= 3;

X
------------------------------------------------------------------------
x_1
x_2
x_3

SQL> select 'y_' || level as y
  2  from dual
  3  connect by level <= 2;

Y
------------------------------------------------------------------------
y_1
y_2

You may need an insert as select by using a cross join over your queries:

SQL> insert into yourTable(x, y)
  2  select x, y
  3  from (
  4          select 'x_' || level as x
  5          from dual
  6          connect by level <= 3
  7       ) table_X
  8       CROSS JOIN
  9       (
 10          select 'y_' || level as y
 11          from dual
 12          connect by level <= 2
 13       ) table_Y;

6 rows created.

The result:

SQL> select * from yourTable;

X          Y
---------- ----------
x_1        y_1
x_1        y_2
x_2        y_1
x_2        y_2
x_3        y_1
x_3        y_2

6 rows selected.

PaW · Accepted Answer · 2018-08-28 10:35:44Z

0

You just need to do the following (without any join condition ) which will create a cartesian product of the 2 tables:

insert into <target_table>
select x.*, y.*
from x, y;

answered Aug 28, 2018 at 10:35

PaW

6694 silver badges7 bronze badges

2 Comments

mr_creeppl Over a year ago

Thank you, that was helpful and easy. I had to select from the same table resources from two different zones using WHERE statement and then just inserted it into table2. Additionally I had defined costs and other colums

insert into table2 (res1, res2, cost, date, user) select x.res, y.res, 0, (select sysdate from dual), (select user from dual) from table1 x, table1 y where x.zone like 'zone1' and y.zone like 'zone2';

PaW Over a year ago

Great. You can use "sysdate" and "user" as is without using the select....from dual. You don't need the select....from dual since they are pseudo-columns. It will make your query a bit faster.

Collectives™ on Stack Overflow

How to insert multiple rows using PL/SQL select

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related