Oracle insert from multiple selects

Question

I'm trying to find a way to insert into an amalgamated table the results from multiple selects. A pseudo example below...

SELECT T1.VAL1,T1.VAL2,T1.VAL3 FROM TABLE1 T1
SELECT T2.VAL4,T2.VAL5,T2.VAL6 FROM TABLE2 T2

FOR ALL T1 LOOP
    FOR ALL T2 LOOP
        INSERT INTO TABLE3 T3
        (COL1,COL2,COL3,COL4,COL5,COL6)
        VALUES
        (T1.VAL1,T1.VAL2,T1.VAL3,T2.VAL4,T2.VAL5,T2.VAL6)
    END
END

So if T1 returns 25 rows and T2 returns 100, I will get a total of 2,500 rows inserted into T3.

Thanks in advance.

K

Kaushik Nayak · Accepted Answer · 2017-11-01 11:52:32Z

1

You can use CROSS JOIN. There is no need of LOOP.

INSERT INTO table3 t3 (
    col1,
    col2,
    col3,
    col4,
    col5,
    col6
)
    SELECT
        t1.val1,
        t1.val2,
        t1.val3,
        t2.val4,
        t2.val5,
        t2.val6
    FROM
        table1 t1
        CROSS JOIN table2 t2;

answered Nov 1, 2017 at 11:52

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

Oracle insert from multiple selects

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related