PL/SQL INSERT Statement

The PL/SQL INSERT statement is vital for adding new records to a database table. By specifying the table’s name and providing values for its columns, users can populate their database with essential information. This functionality enables efficient data entry and ensures the completeness of datasets, facilitating various database operations and analyses.

PL/SQL INSERT Statement

In the world of database management systems, the INSERT PL/SQL statement allows adding new data into database tables. It allows programmers to insert data in an organized way which ultimately improves the form of interaction with database systems

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

Here,

• table_name: Name of the table where data will be inserted.
• (column1, column2, …): Names of the columns where data will be inserted.
• VALUES (value1, value2, …): Values to be inserted into the respective columns listed in the (column1, column2, …) clause.

Example of PL/SQL INSERT Statement

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

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:

+-------------+------------+-----------+------------+
| employee_id | first_name | last_name | department |
+-------------+------------+-----------+------------+
|         101 | John       | Doe       | Sales      |
+-------------+------------+-----------+------------+

Explanation: The output displays a single row representing an employee with an ID of 101, first name ‘John‘, last name ‘Doe‘, and department ‘Sales‘. Each column’s value aligns with the respective column headers: employee_id, first_name, last_name, and department.

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. undefined

For 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 to the employees table.

-- Inserting multiple rows into the 'employees' table
-- Every line represents employee_id, first_name, last_name, and department as its constituent components.
   INSERT INTO employees (employee_id, first_name,last_name, department)
VALUES
(102, 'Alice', 'Smith', 'Marketing')-- Row 1
(103, 'Bob', 'Johnson', 'Finance') -- Row 2
(104, 'Emily', 'Brown' , 'HR'); -- Row 3

-- Creating a new table named 'Newemployees' with the same structure as the 'employees' table

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

-Select All rows in the 'employees' table and put data into the 'Newemployee' table.
-- The condition '1 != 2' is always true and it won't filter any rows in the data set.
INSERT INTO Newemployees
SELECT * FROM employees
WHERE 1 != 2;

Output:

+-------------+------------+-----------+------------+
| employee_id | first_name | last_name | department |
+-------------+------------+-----------+------------+
|         102 | Alice      | Smith     | Marketing  |
|         103 | Bob        | Johnson   | Finance    |
|         104 | Emily      | Brown     | HR         |
+-------------+------------+-----------+------------+

Explanation: The output displays a new table named Newemployees, identical to the employees table, with employee data copied over. Newemployees now contain three rows, each representing an employee’s ID, first name, last name, and department, derived from the employees table. This action ensures Newemployees mirror the original employee records without any filtering or alterations.

Conclusion

An insert statement within PL/SQL is a flexible, as well as efficient, way of putting new data into the table of the database. Developers are provided with the INSERT statement with which the data entry can automatically be inserted into the database, the data can be retrieved from another table or multiple rows can be inserted at the same time; in this way, data management processes are enhanced, the systems are made more functional and the development process is much more organized.