How to Insert Multiple Rows in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Oracle 19c/21c reject multi-row VALUES lists; 23ai added support. For 19c/21c, use INSERT ALL for literals or INSERT ... SELECT to copy rows.
INSERT ALL with literals
INSERT ALL lets you list several INSERT clauses that all run against a single pass over a source; SELECT * FROM dual supplies exactly one dummy row to drive them.
INSERT ALL
INTO orders (order_id, customer_id, order_total) VALUES (101, 1, 250.00)
INTO orders (order_id, customer_id, order_total) VALUES (102, 2, 89.50)
INTO orders (order_id, customer_id, order_total) VALUES (103, 1, 42.75)
SELECT * FROM dual;Insert from a query
For rows coming from another table or a computed result set, INSERT ... SELECT is the normal approach and scales far better than INSERT ALL.
INSERT INTO orders_archive (order_id, customer_id, order_total)
SELECT order_id, customer_id, order_total
FROM orders
WHERE order_date < DATE '2025-01-01';Developers coming from MySQL or Postgres often try VALUES (1,'a'), (2,'b') directly, which Oracle 19c and 21c reject with a syntax error; Oracle 23ai added support for exactly this multi-row VALUES form, so the restriction only applies to older versions. On 19c and 21c, INSERT ALL is the closest equivalent, but Oracle still evaluates every INTO clause for every row of the driving query, so the statement text and its parsing cost grow with the row count; past a few dozen literal rows it is worth generating INSERT ... SELECT FROM dual UNION ALL statements or using array binding from application code instead.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free