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:
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:
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.
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.
Contact Us