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.