Insert data into tables - SQL

#sql #database #backend #fullstack

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';