Oracle Database INSERT Statements Tutorial - VALUES, SELECT & Multi-Table

Oracle SQL | INSERT Statements

Sample Data Setup
Basic INSERT Operations
Multi-Table INSERT Operations
1. INSERT ALL
2. INSERT FIRST

Sample Data Setup

These tables and sample data are used in the examples below:

CREATE TABLE table_insert_1
(
    id1 NUMBER(2,0)
,   desc1 VARCHAR2(2)
);

CREATE TABLE table_insert_2
(
    id1 NUMBER(2,0)
,   desc1 VARCHAR2(2)
);

CREATE TABLE table_insert_3
(
    id1 NUMBER(2,0)
,   desc1 VARCHAR2(2)
);

CREATE TABLE table_insert_4
(
    id1 NUMBER(2,0)
,   desc1 VARCHAR2(2)
);

Basic INSERT Operations
Oracle provides several methods for inserting data into tables. These range from simple value insertion to more complex operations involving subqueries and conditional logic.
1. INSERT ... VALUES
  The most basic form of INSERT statement allows you to specify explicit values for each column. This method is ideal for inserting single rows with known values. Column names can be specified explicitly or omitted if values are provided for all columns in table order.
  Example:
```
INSERT INTO table_insert_1 (id1, desc1)
VALUES (1, 'A');
```
  Select data from the table "table_insert_1":
```
SELECT id1, desc1 FROM table_insert_1;
```
  Query result:
```
id1   desc1
----- -----
1     A
```
2. INSERT ... SELECT
  This method allows you to insert data derived from expressions or calculations. The SELECT clause can include literal values, function calls, or calculations. Using DUAL is common when inserting literal values, as it provides a single-row result set.
  Example:
```
INSERT INTO table_insert_1 (id1, desc1)
SELECT 2, 'B' FROM dual;
```
  Select data from the table "table_insert_1":
```
SELECT id1, desc1 FROM table_insert_1;
```
  Query result:
```
id1   desc1
----- -----
1     A
2     B
```
3. INSERT ... SELECT ... WHERE
  This variation adds conditional logic to control when data is inserted. The WHERE clause can include subqueries to check for existing data, preventing duplicate insertions or implementing business rules. The EXISTS operator is commonly used to verify whether related data already exists.
  Example:
```
INSERT INTO table_insert_1 (id1, desc1)
SELECT 3, 'C' FROM dual
WHERE NOT EXISTS (SELECT 1 FROM table_insert_1 WHERE id1 = 3);
```
  Select data from the table "table_insert_1":
```
SELECT id1, desc1 FROM table_insert_1;
```
  Query result:
```
id1   desc1
----- -----
1     A
2     B
3     C
```
Multi-Table INSERT Operations
Oracle Database provides specialized syntax for inserting data into multiple tables simultaneously. These multi-table INSERT statements allow you to distribute data from a single source query across multiple target tables based on conditional logic.
1. INSERT ALL
  The INSERT ALL statement evaluates all WHEN conditions for each row from the source query. If multiple conditions are true for a single row, that row will be inserted into multiple tables.
  
  Example:
```
INSERT ALL
WHEN id1 = 1 THEN INTO table_insert_2
WHEN id1 = 2 THEN INTO table_insert_3
ELSE INTO table_insert_4
SELECT id1, desc1 FROM table_insert_1;
```
  - Select data from the table "table_insert_2":
    
    SELECT id1, desc1 FROM table_insert_2;
    Query result:
    
    id1 desc1 ----- ----- 1 A
  - Select data from the table "table_insert_3":
    
    SELECT id1, desc1 FROM table_insert_3;
    Query result:
    
    id1 desc1 ----- ----- 2 B
  - Select data from the table "table_insert_4":
    
    SELECT id1, desc1 FROM table_insert_4;
    Query result:
    
    id1 desc1 ----- ----- 3 C
  Note: All WHEN clauses are evaluated for each row returned by the subquery. If multiple conditions evaluate to true for the same row, the insert statements will be executed for all matching WHEN clauses. This can result in the same data being inserted into multiple tables.
  
  To illustrate this behavior, consider the following example with overlapping conditions:
```
INSERT ALL
WHEN id1 < 2 THEN INTO table_insert_2
WHEN id1 < 3 THEN INTO table_insert_3
ELSE INTO table_insert_4
SELECT id1, desc1 FROM table_insert_1;
```
  - Select data from the table "table_insert_2":
    
    SELECT id1, desc1 FROM table_insert_2;
    Query result:
    
    id1 desc1 ----- ----- 1 A
  - Select data from the table "table_insert_3":
    
    SELECT id1, desc1 FROM table_insert_3;
    Query result:
    
    id1 desc1 ----- ----- 1 A 2 B
  - Select data from the table "table_insert_4":
    
    SELECT id1, desc1 FROM table_insert_4;
    Query result:
    
    id1 desc1 ----- ----- 3 C
2. INSERT FIRST
  The INSERT FIRST statement provides an alternative behavior where WHEN clauses are evaluated sequentially, and only the first matching condition is executed for each row. Once a condition evaluates to true, the remaining WHEN clauses are skipped for that row. This ensures that each source row is inserted into exactly one target table.
```
INSERT FIRST
WHEN id1 < 2 THEN INTO table_insert_2
WHEN id1 < 3 THEN INTO table_insert_3
ELSE INTO table_insert_4
SELECT id1, desc1 FROM table_insert_1;
```
  - Select data from the table "table_insert_2":
    
    SELECT id1, desc1 FROM table_insert_2;
    Query result:
    
    id1 desc1 ----- ----- 1 A
  - Select data from the table "table_insert_3":
    
    SELECT id1, desc1 FROM table_insert_3;
    Query result:
    
    id1 desc1 ----- ----- 2 B
  - Select data from the table "table_insert_4":
    
    SELECT id1, desc1 FROM table_insert_4;
    Query result:
    
    id1 desc1 ----- ----- 3 C