CODEWITHSUNDEEP
sqloracle-databaseselectinsertcursor
mr_creeppl
mr_creeppl

Reputation: 11

How to insert multiple rows using PL/SQL select

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.

Upvotes: 0

Views: 190

Answers (2)

PaW
PaW

Reputation: 669

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;

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

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.

Upvotes: 2

Related Questions