PL/SQL INSERT INTO SELECT

In PL/SQL, the INSERT INTO SELECT statement is used to insert data into a table by selecting data from one or more tables. This is a powerful feature for populating tables with data from existing tables or views, making it useful for data migration, reporting, and backup processes.

In this guide, we will learn how to use PL/SQL INSERT INTO SELECT statements with various examples.

PL/SQL INSERT INTO SELECT

The INSERT INTO SELECT statement allows us to insert data into a table by selecting and retrieving data from another table or query result.

This is especially useful when we need to copy data or aggregate data from multiple sources into a new table. It can handle both the insertion of specific columns and the transformation of data as it is inserted.

Syntax:

INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;

Explanation:

• target_table: The table where data will be inserted.
  
• column1, column2, column3, ... : It represent the Columns in the target table to receive data.
  
• source_table: The table from which data will be selected.
  
• condition: Specifies which rows to select from the source table.

Examples of PL/SQL INSERT INTO SELECT

To understand the INSERT INTO SELECT functionality in PL/SQL, we’ll use two tables: employees and archived_employees. We will copy data from the employees table to the archived_employees table for employees who meet specific criteria (e.g., employees who are no longer with the company).

employees table:

The provided query creates an employees table with three columns: emp_id, emp_name, and status. It then inserts four records into the table, each representing an employee with a unique ID, name, and employment status.

Query:

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    status VARCHAR2(20)
);

INSERT INTO employees (emp_id, emp_name, status) VALUES (1, 'Alice Johnson', 'Active');
INSERT INTO employees (emp_id, emp_name, status) VALUES (2, 'Bob Smith', 'Inactive');
INSERT INTO employees (emp_id, emp_name, status) VALUES (3, 'Charlie Brown', 'Active');
INSERT INTO employees (emp_id, emp_name, status) VALUES (4, 'Diana Prince', 'Inactive');

Output:

Explanation:

The output displays the contents of the employees table after the insertions. It lists four employees with their respective IDs, names, and statuses:

• Alice Johnson and Charlie Brown are marked as Active.
• Bob Smith and Diana Prince are marked as Inactive.

This setup reflects the initial state of the employees table, showing both active and inactive employees.

archived_employees table:

The query creates a new table named archived_employees with the same structure as the employees table, including columns for emp_id, emp_name, and status. This table is intended to store records of employees who are no longer with the company.

Query:

CREATE TABLE archived_employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    status VARCHAR2(20)
);

Output:

Explanation:

The output displays the schema of the archived_employees table after its creation. Since no data has been inserted yet, the table is empty, but it is ready to store employee records with the same structure as the employees table.

Example 1: Using the VALUES Keyword

The INSERT INTO archived_employees statement uses the VALUES keyword to insert a new row into the archived_employees table with explicit values for emp_id, emp_name, and status.

This does not involve data from another table rather, it directly inserts the provided values into the specified columns

INSERT INTO archived_employees (employee_id, name, department, status)
VALUES (4, 'Bob Martin', 'Marketing', 'Inactive');

Output:

Explanation:

The output shows that the archived_employees table now contains one new record with emp_id 4, emp_name 'Bob Martin', and status 'Inactive'. This data was inserted directly using the VALUES clause.

Example 2: Using the SELECT Statement

The INSERT INTO SELECT statement is used to copy data from the employees table to the archived_employees table.

The query selects all columns for employees with a status of 'Inactive' and inserts them into archived_employees. This allows for bulk copying of records based on the specified condition.

INSERT INTO archived_employees (employee_id, name, department, status)
SELECT employee_id, name, department, status
FROM employees
WHERE status = 'Inactive';

Output:

Explanation:

The archived_employees table now includes the records of all employees with the status 'Inactive' from the employees table. This demonstrates how data can be efficiently transferred between tables based on specific criteria.

Conclusion

The INSERT INTO SELECT statement is a valuable tool in PL/SQL for transferring and manipulating data between tables. It allows us to efficiently move data based on specific conditions or queries, making it an essential feature for database management and operations. By understanding how to use INSERT INTO SELECT, we can streamline data handling processes and enhance the functionality of your database systems