How to Insert Multiple Rows at Once in PL/SQL?

As the volume and complexity of data continue to grow in modern systems, efficient data management techniques become important. One fundamental operation in database management is the insertion of multiple rows at once. In this article, we understand the techniques and methods available in PL/SQL for inserting multiple rows simultaneously. We will INSERT ALL statements, which allow the insertion of multiple rows in a single query. We will also discuss the INSERT…SELECT statement, which facilitates the insertion of rows by selecting data from another table or query result.

Introduction to Insert in PL/SQL

The INSERT statement in PL/SQL is a fundamental tool for adding new records to database tables. It allows developers to insert one or more rows of data into a specified table, either by providing explicit values or by selecting values from another table or query result. The syntax of the INSERT statement is straightforward.

Syntax:

INSERT INTO table_name (column1, column2, ..., column_n)
VALUES (value1, value2, ..., value_n);

Explanation:

• table_name is the name of the table into which the data will be inserted.
• (column1, column2, ..., column_n) specifies the columns in the table where the data will be inserted.
• VALUES (value1, value2, ..., value_n) represents the values to be inserted into the corresponding columns. The number of values provided must match

Setting Up Environment

Let us start by creating a sample table. We will create an employee table with fields like employee_id, employee_name, and city. The following query creates the table:

Query:

CREATE TABLE employees
( 
    employee_id number(10) NOT NULL,
    employee_name varchar2(50) NOT NULL,
    city varchar2(50)
);

We are going to have a look at two methods in this article to go about inserting multiple records in the table.

Ways to Insert Multiple Rows at Once in PL/SQL

Method 1: Using INSERT ALL

The INSERT ALL statement is used to insert multiple records into the table using a single query.

Syntax:

INSERT ALL  
  INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
  INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
  INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
SELECT * FROM dual;  

Explanation:

• table_name: The name of the table.
• column1, column2, column_n: The name of the columns.
• expr1, expr2, expr_n: The values to be inserted.

Example: Let’s inserts 3 records in the employees table

The following query inserts 3 records in the employees table.

Query:

INSERT ALL INTO employees
(employee_id, employee_name, city)
VALUES (1, 'Jack', 'New York') INTO employees
(employee_id, employee_name, city)
VALUES (2, 'Jill', 'Los Angeles') INTO employees
(employee_id, employee_name, city)
VALUES (3, 'Jim', 'Las Vegas')
SELECT * FROM DUAL;

The following query prints the contents of the table after the insert operation:

Query:

DECLARE
    r_emp employees%ROWTYPE;
    CURSOR emp_cur is
    SELECT *
    FROM employees;
BEGIN
    FOR r_emp IN emp_cur
    LOOP
        DBMS_OUTPUT.PUT_LINE( 'id: ' || r_emp.employee_id ||', name: ' || r_emp.employee_name || ', city: ' || r_emp.city );
    END LOOP;
END;

Output:

Output

Explanation: In the above PL/SQL query we utilizes the INSERT ALL statement to efficiently insert multiple rows into the ‘employees‘ table with predefined values. Subsequently, a cursor is created to fetch the inserted data, and each record’s details, including employee ID, name, and city, are displayed using the DBMS_OUTPUT.PUT_LINE procedure.

Method 2: Using INSERT…SELECT

The INSERT…SELECT statement is used to insert multiple records into the table using a single query.

Syntax:

INSERT INTO dest_table_name(column1, column2, column_n) 
SELECT expr1, expr2, expr_n FROM src_table_name

Explanation:

• dest_table_name: The name of the table in which to insert.
• src_table_name: The name of the table from which to insert.
• column1, column2, column_n: The name of the columns.
• expr1, expr2, expr_n: The values to be inserted.

Example

The following query inserts 3 records in the employees table.

Query:

INSERT INTO employees
(employee_id, employee_name, city)
SELECT 1, 'Jack', 'New York' FROM DUAL
UNION ALL
SELECT 2, 'Jill', 'Los Angeles' FROM DUAL
UNION ALL
SELECT 3, 'Jim', 'Las Vegas' FROM DUAL;

The following query prints the content of the table after insertion:

Query:

DECLARE
    r_emp employees%ROWTYPE;

    CURSOR emp_cur is
    SELECT *
    FROM employees;
BEGIN
    FOR r_emp IN emp_cur
    LOOP
        DBMS_OUTPUT.PUT_LINE( 'id: ' || r_emp.employee_id ||', name: ' || r_emp.employee_name || ', city: ' || r_emp.city );
    END LOOP;
END;

Output:

Output

Explanation: In the above PL/SQL query we utilizes the INSERT INTO statement in combination with SELECT and UNION ALL clauses to insert multiple rows of data into the ‘employees‘ table. Subsequently, a cursor is created to fetch the inserted data, and each record’s details, including employee ID, name, and city, are displayed using the DBMS_OUTPUT.PUT_LINE procedure.

Conclusion

Overall, After reading whole article now you have good understanding about how to insert multiple row at once through various methods like Using INSERT ALL and Using INSERT…SELECT method. We have implemented these method and saw the example along with the output and their explanations. Now you can easily use these method and insert records into the tables easily.