Oracle SQL inserting multiple rows and returning something

Question

In Oracle, one can insert multiple rows by doing a query like

INSERT ALL
   INTO mytable (column1, column2, column3) VALUES ('val1.1', 'val1.2', 'val1.3')
   INTO mytable (column1, column2, column3) VALUES ('val2.1', 'val2.2', 'val2.3')
   INTO mytable (column1, column2, column3) VALUES ('val3.1', 'val3.2', 'val3.3')
SELECT * FROM dual;

And with a prepared statement, doing a single insert like this one

BEGIN INSERT 
   INTO mytable (column1, column2, column3) VALUES (null, 'val1.2', 'val1.3')
RETURNING column1 INTO ?; END;

will result in returning column1's value (supposing there is a trigger assigning a value to it before insert).

Is there a way, if possible at all, to combine both? Meaning, inserting multiple values while still returning all the column1 values (a resultset) with a single query?

The BULK COLLECT INTO synthax is not supported for INSERT as of 10gR2 (example here). I've not tested it with more recent versions of Oracle. — Vincent Malgrat
– Vincent Malgrat, Commented Apr 28, 2011 at 8:32

Craig · Accepted Answer · 2021-05-25 21:12:40Z

4

From the documentation (at least through the 21c version), one of the restrictions to the returning clause:

You cannot specify the returning_clause for a multitable insert.

edited May 25, 2021 at 21:12

answered Apr 28, 2011 at 13:58

Craig

5,87024 silver badges31 bronze badges

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

6 Comments

Yanick Rochon Over a year ago

perhaps, but there should be way to execute a block and return a resultset of all the single inserts

tbone Over a year ago

if you need the individual returning into values for each insert statement, why not just run them, well, individually?

Yanick Rochon Over a year ago

I was trying to avoid creating a new query (and request to the db) for every row and use some batch command... I guess a solution would be lock the table, insert every row in a block statement and then sending a query to fetch the new generated keys (the last n inserted rows) and finally unlocking the table. Does this sound right?

Yanick Rochon Over a year ago

choosing your answer as accepted to close this question. Thanks!

Kiruahxh Over a year ago

Multiple insert queries is slow, I agree with Yanick

|

Collectives™ on Stack Overflow

Oracle SQL inserting multiple rows and returning something

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related