Oracle INSERT data from variables-CodePudding

I'm looking for some help with Oracle.

I have a query like this

SELECT column1 FROM table1 WHERE column2 = 'columndata';

in my query, this result returns multiple rows. I would like to use the results in multiple insert statements in the same query.

ps. my actually query for the SELECT statement is large and complex.

i.e.

INSERT INTO table2 values (data from previous SELECT);
INSERT INTO table3 values (data from previous SELECT);
INSERT INTO table4 values (data from previous SELECT);
INSERT INTO table5 values (data from previous SELECT);
etc etc...

How can I accomplish this using variables? p.s. I can't use temp table due to permissions.

I've tried searching google but I'm not getting the results I need. thanks Craig.

CodePudding user response：

Sounds like you need an INSERT ALL statement, e.g.:

INSERT ALL
  INTO table2 (col1, col2, ...) VALUES (source_col1, source_col2, ...)
  INTO table3 (col1, col2, ...) VALUES (source_col1, source_col2, ...)
  INTO table4 (col1, col2, ...) VALUES (source_col1, source_col2, ...)
  INTO table5 (col1, col2, ...) VALUES (source_col1, source_col2, ...)
SELECT source_col1,
       source_col2,
       ...
FROM   ...;

You would need to amend the list of columns being inserted to and from accordingly, obviously.