Database engine independent INSERT of multiple rows
539132Oct 12 2006 — edited Oct 13 2006In our product we try to support multiple database engines while using as much standard SQL statements as possible. Our database has around 50 tables and our queries are generally pretty straightforward.
Our initial database was MySQL, but now we successfully run the product on Apache Derby and we are also trying to support Oracle 10g Release 1.
MySQL has a feature of multiple row inserts inside a single INSERT statement.
INSERT INTO mytable(col1, col2, col3) VALUES
(val11, val12, val13),
(val21, val22, val23);
On MySQL the multiple row insert is much more efficient than splitting it into separate inserts. I found something similar available in Oracle since release 9i:
INSERT ALL
INTO mytable(col1, col2, col3) VALUES
(val11, val12, val13)
INTO mytable(col1, col2, col3) VALUES
(val21, val22, val23)
SELECT 1 FROM DUAL;
Of course this is far from portable. Does anyone know a portable solution to multiple row inserts?
Fixed invalid Oracle multiple row insert.
Message was edited by:
user536129