CODEWITHSUNDEEP
sqloracle-databaseloopssql-insert
Fab Esc
Fab Esc

Reputation: 13

Insert LOOP for Oracle

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

  1. Rownumber
  2. Primary Key - Created by sequence
  3. Foreign Key from a entry in EXPTB1
  4. Position

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

Cheers Fabian

Upvotes: 1

Views: 328

Answers (2)

MT0
MT0

Reputation: 167902

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;

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132570

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.

Upvotes: 2

Related Questions