How Can I Efficiently Insert Multiple Rows into an Oracle Database?

Efficient batch insertion method of data in Oracle database

There are many ways to insert multiple rows of data into Oracle database. The batch insert method commonly used in MySQL is not directly supported in Oracle.

Use INSERT ALL statement

In Oracle, you can use the INSERT ALL statement for batch insertion. This statement contains multiple INTO clauses, each clause represents a row of data to be inserted. The statement ends with SELECT 1 FROM DUAL as a sign of the end of the insertion.

<code class="language-sql">INSERT ALL
   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
   .
   .
   .
SELECT 1 FROM DUAL;</code>

Oracle 23c simplified syntax

Oracle 23c introduces a simplified bulk insert syntax that eliminates the need for the INSERT ALL statement. Multiple rows of data can be inserted using a comma separated list like this:

<code class="language-sql">INSERT INTO t(col1, col2, col3) VALUES
('val1_1', 'val1_2', 'val1_3'),
('val2_1', 'val2_2', 'val2_3'),
('val3_1', 'val3_2', 'val3_3');</code>

Performance Considerations

For large amounts of data, performance issues must be considered. The new INSERT syntax in Oracle 23c is much faster than INSERT ALL and has comparable performance to the UNION ALL method. However, inserting more than about 1000 rows of data at a time causes parsing time to increase exponentially.

The above is the detailed content of How Can I Efficiently Insert Multiple Rows into an Oracle Database?. For more information, please follow other related articles on the PHP Chinese website!