Insert data into tables - SQL

In Oracle, there are several ways to insert data into tables. Below are some specific methods for inserting data in Oracle.

1. Basic INSERT INTO ... VALUES

This is the most straightforward way to insert data into a table in Oracle.

Syntax:

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

Example:

INSERT INTO employees (employee_id, name, department, salary)
VALUES (1, 'Alice', 'HR', 5000);

Inserts a single row into the employees table.

---

2. Inserting Multiple Rows using INSERT ALL

Oracle provides the INSERT ALL statement to insert multiple rows into a table in a single SQL query. This is Oracle-specific and quite useful when inserting many rows.

Syntax:

INSERT ALL
    INTO table_name (column1, column2, column3, ...) 
    VALUES (value1, value2, value3, ...)
    INTO table_name (column1, column2, column3, ...) 
    VALUES (value4, value5, value6, ...)
SELECT * FROM dual;

Example:

INSERT ALL
    INTO employees (employee_id, name, department, salary) 
    VALUES (2, 'Bob', 'HR', 6000)
    INTO employees (employee_id, name, department, salary)
    VALUES (3, 'Charlie', 'IT', 7000)
    INTO employees (employee_id, name, department, salary) 
    VALUES (4, 'David', 'IT', 8000)
SELECT * FROM dual;

The dual table is a special dummy table in Oracle used to execute queries that don’t need to query real tables (like in this case).
Inserts multiple rows in a single query.

---

3. INSERT INTO ... SELECT
This method allows you to insert data into a table by selecting data from another table. This is useful for copying data between tables.

Syntax:

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

Example:

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

Inserts data from the employees table into the archived_employees table, but only for employees in the IT department.