PL/SQL INSERT Statement

The PL/SQL INSERT statement is used to add new records into a database table, ensuring that data is stored in a structured and complete manner. It helps in tasks such as:

• Adding new rows to database tables.
• Storing important data for applications.
• Keeping datasets complete and accurate.
• Supporting database operations and analysis.

Syntax:

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

• table_name: The table where data will be inserted.
• (column1, column2, …) : Columns that will receive the data.
• VALUES (value1, value): The values added to those columns.

Example of PL/SQL INSERT Statement

Example: Create a table named employees with columns employee_id, first_name, last_name, and department

Query:

CREATE TABLE employees (
 employee_id INT,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 department VARCHAR(50)
);

INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES (101, 'John', 'Doe', 'Sales');

Output:

Example of Using SELECT Statement

The PL/SQL INSERT statement can also act based on the SELECT query, which is used to select data from another table.

Example: Assume we have another table of name New_employees with the same columns as the employees table. We would like to load data in the New_employees table from the employees table.

Query:

INSERT INTO employees (employee_id, first_name, last_name, department)
VALUES
(102, 'Alice', 'Smith', 'Marketing'),
(103, 'Bob', 'Johnson', 'Finance'),
(104, 'Emily', 'Brown', 'HR');

CREATE TABLE New_employees (
employee_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50)
);

INSERT INTO New_employees
SELECT * FROM employees
WHERE 1 != 2;

Output:

• Adds multiple records into the employees table.
• Creates a new table New_employees with the same column structure.
• The WHERE clause is used to filter rows based on a condition.
• WHERE 1 != 2 is always TRUE, so it selects all records without filtering.