Inserting Multiple Rows into an Oracle Database

Inserting multiple rows into an Oracle database can be achieved through various methods. In this tutorial, we will explore the different approaches to accomplish this task.

Method 1: Using INSERT ALL Statement

The INSERT ALL statement allows you to insert multiple rows into a table in a single operation. The syntax for this statement is as follows:

INSERT ALL
   INTO table_name (column1, column2, column3) VALUES ('val1_1', 'val1_2', 'val1_3')
   INTO table_name (column1, column2, column3) VALUES ('val2_1', 'val2_2', 'val2_3')
   INTO table_name (column1, column2, column3) VALUES ('val3_1', 'val3_2', 'val3_3')
SELECT 1 FROM DUAL;

This method is useful when you need to insert a large number of rows into a table.

Method 2: Using Simplified INSERT Statement (Oracle 23c and later)

In Oracle 23c and later versions, you can use the simplified INSERT statement to insert multiple rows into a table. The syntax for this statement is as follows:

INSERT INTO table_name (column1, column2, column3) VALUES
   ('val1_1', 'val1_2', 'val1_3'),
   ('val2_1', 'val2_2', 'val2_3'),
   ('val3_1', 'val3_2', 'val3_3');

This method is more concise and easier to read than the INSERT ALL statement.

Method 3: Using UNION ALL Statement

You can also use the UNION ALL statement to insert multiple rows into a table. The syntax for this statement is as follows:

INSERT INTO table_name (column1, column2, column3)
SELECT 'val1_1', 'val1_2', 'val1_3' FROM DUAL
UNION ALL
SELECT 'val2_1', 'val2_2', 'val2_3' FROM DUAL
UNION ALL
SELECT 'val3_1', 'val3_2', 'val3_3' FROM DUAL;

This method is useful when you need to insert rows from different sources into a table.

Method 4: Using SQL*Loader

SQLLoader is a powerful tool for loading data into an Oracle database. You can use it to load data from a file or other data source into a table. The syntax for using SQLLoader is as follows:

LOAD DATA
INFILE 'data_file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(column1, column2, column3);

This method is useful when you need to load a large amount of data into a table.

Method 5: Using PL/SQL Block

You can also use a PL/SQL block to insert multiple rows into a table. The syntax for this statement is as follows:

DECLARE
   PROCEDURE ins (
      p_column1 VARCHAR2,
      p_column2 VARCHAR2,
      p_column3 VARCHAR2
   ) IS
   BEGIN
      INSERT INTO table_name (column1, column2, column3) VALUES (p_column1, p_column2, p_column3);
   END;
BEGIN
   ins ('val1_1', 'val1_2', 'val1_3');
   ins ('val2_1', 'val2_2', 'val2_3');
   ins ('val3_1', 'val3_2', 'val3_3');
END;
/

This method is useful when you need to insert rows into a table using a PL/SQL procedure.

Best Practices

When inserting multiple rows into an Oracle database, it’s essential to follow best practices to ensure optimal performance and data integrity. Here are some tips:

• Use the INSERT ALL statement or simplified INSERT statement (Oracle 23c and later) for large inserts.
• Use SQL*Loader for loading data from files or other data sources.
• Avoid using multiple single-row insert statements, as this can lead to performance issues.
• Always specify the columns being inserted into to avoid errors.

By following these methods and best practices, you can efficiently insert multiple rows into an Oracle database.