CODEWITHSUNDEEP
oracle-databaseinsert
KS1
KS1

Reputation: 1039

Oracle insert from multiple selects

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

Upvotes: 0

Views: 60

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

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;

Upvotes: 1

Related Questions