7

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?

Yanick Rochon
  • 51,409
  • 25
  • 133
  • 214
  • The [BULK COLLECT INTO](http://download.oracle.com/docs/cd/B19306%5F01/appdev.102/b14261/tuning.htm#sthref2236) synthax is not supported for INSERT as of 10gR2 (example [here](http://stackoverflow.com/questions/1074843/is-there-an-oracle-equivalent-to-sql-servers-output-inserted/1075198#1075198)). I've not tested it with more recent versions of Oracle. – Vincent Malgrat Apr 28 '11 at 08:32

1 Answers1

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.

Craig
  • 5,740
  • 21
  • 30
  • 1
    perhaps, but there should be way to execute a block and return a resultset of all the single inserts – Yanick Rochon Apr 28 '11 at 14:29
  • if you need the individual returning into values for each insert statement, why not just run them, well, individually? – tbone Apr 28 '11 at 19:56
  • 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 Apr 29 '11 at 14:33
  • choosing your answer as accepted to close this question. Thanks! – Yanick Rochon Jul 14 '11 at 04:26
  • Multiple insert queries is slow, I agree with Yanick – Kiruahxh Mar 20 '21 at 11:04
  • As an FYI, you can use FORALL and RETURNING .. BULK COLLECT INTO to do this, but that will mean you have to get your data into a collection before doing your insert, which may or may not be worth the trouble depending on how you are generating these statements. – Craig Mar 24 '21 at 19:36