Insert LOOP for Oracle

Question

I need a solution for mi problem. It doesn't necessarily have to be a Loop, i'm thankfull for every Input.

I have a Table with 262 entries in it. Let's call it EXPTB1. And i have a second Table EXPTB2 which is emtpy.

Now for each entry in EXPTB1 i need 6 entries in EXPTB2. And three Attributes will have to be filled. One is a Primary key, which is automaticly generated by a sequence. One is the foreign key of the entry of EXPTB1 and one contains six different numbers (Positions) for each of the 6 entries. The numbers would always be 1,2,3,11,12,13.

So i we look at a individual entry of EXPTB1 i need for each of ist objects the following query:

insert into EXPTB2(EXPTB2_ID,EXPTB2_EXPTB1_ID,EXPTB2_POSITION) values (EXPTB2_SEQ.nextval,EXPTB1_ID,1);
insert into EXPTB2(EXPTB2_ID,EXPTB2_EXPTB1_ID,EXPTB2_POSITION) values (EXPTB2_SEQ.nextval,EXPTB1_ID,2);
insert into EXPTB2(EXPTB2_ID,EXPTB2_EXPTB1_ID,EXPTB2_POSITION) values (EXPTB2_SEQ.nextval,EXPTB1_ID,3);
insert into EXPTB2(EXPTB2_ID,EXPTB2_EXPTB1_ID,EXPTB2_POSITION) values (EXPTB2_SEQ.nextval,EXPTB1_ID,11);
insert into EXPTB2(EXPTB2_ID,EXPTB2_EXPTB1_ID,EXPTB2_POSITION) values (EXPTB2_SEQ.nextval,EXPTB1_ID,12);
insert into EXPTB2(EXPTB2_ID,EXPTB2_EXPTB1_ID,EXPTB2_POSITION) values (EXPTB2_SEQ.nextval,EXPTB1_ID,13);

Image of Result

Rownumber
Primary Key - Created by sequence
Foreign Key from a entry in EXPTB1
Position

I hope my description is clear enough, it's a bit difficult to describe.. :)

Cheers Fabian

kfinity · Accepted Answer · 2018-06-13 14:02:32Z

2

Try this:

insert into EXPTB2 (EXPTB2_ID,EXPTB2_EXPTB1_ID,EXPTB2_POSITION)
with positions (pos) as
( select 1 from dual union all
  select 2 from dual union all
  select 3 from dual union all
  select 11 from dual union all
  select 12 from dual union all
  select 13 from dual )
select EXPTB2_SEQ.nextval, EXPTB1_ID, positions.pos
from   EXPTB1
       cross join positions;

The with clause creates a virtual table POSITIONS with 6 rows containing the values you want. This is then cross-joined with the source table to generate 6 new rows for each source table row.

edited Jun 13, 2018 at 14:02

kfinity

9,1411 gold badge15 silver badges22 bronze badges

answered Jun 13, 2018 at 13:56

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fab Esc Over a year ago

Works perfectly fine. Thanks a lot!

MT0 · Accepted Answer · 2018-06-13 14:07:41Z

2

You can CROSS JOIN with a table collection expression containing the values:

INSERT INTO EXPTB2( EXPTB2_ID,EXPTB2_EXPTB1_ID,EXPTB2_POSITION )
SELECT EXPTB2_SEQ.nextval,
       EXPTB1_ID,
       t.COLUMN_VALUE
FROM   EXPTB1
       CROSS JOIN
       TABLE( SYS.ODCINUMBERLIST( 1, 2, 3, 11, 12, 13 ) ) t;

answered Jun 13, 2018 at 14:07

MT0

173k12 gold badges70 silver badges136 bronze badges

1 Comment

Fab Esc Over a year ago

Works perfectly fine. Thanks a lot!

Collectives™ on Stack Overflow

Insert LOOP for Oracle

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related