Inserting Multiple Rows Using a Single Statement

View All Scripts Login to Run Script

Script Name Inserting Multiple Rows Using a Single Statement
Description This example creates three tables and them uses different INSERT statements to insert data into these tables. Multiple rows are inserted into a table using the INSERT ALL statement and by using the inserting the results of the select query.
Area SQL General / Data Manipulation
Referenced In Database SQL Language Reference
Contributor Oracle
Created Monday October 05, 2015

Statement 1

This statement creates the PEOPLE table. The columns person_id, given_name, and family_name have the NOT NULL constraint indicating that these columns must always have a value.

Create table PEOPLE

CREATE TABLE people ( 
  person_id   INTEGER NOT NULL PRIMARY KEY, 
  given_name  VARCHAR2(100) NOT NULL, 
  family_name VARCHAR2(100) NOT NULL, 
  title       VARCHAR2(20), 
  birth_date  DATE 
)

Table created.

Statement 2
This statement creates the PATIENTS table. The PATIENT_ID column is a foreign key and it references the PERSON_ID column of the PEOPLE table.
Create table PATIENTS
```
CREATE TABLE patients ( 
  patient_id          INTEGER NOT NULL PRIMARY KEY REFERENCES people (person_id), 
  last_admission_date DATE 
)
```
```
Table created.
```
Statement 3
This statement creates the STAFF table. The STAFF_ID column is a foreign key and it references the PERSON_ID column of the PEOPLE table.
Create table STAFF
```
CREATE TABLE staff ( 
  staff_id   INTEGER NOT NULL PRIMARY KEY REFERENCES people (person_id), 
  hired_date DATE 
)
```
```
Table created.
```
Statement 4
This statement inserts a row into the PEOPLE table. It provides values for the all the columns in the table.
Insert a row into the PEOPLE table
```
INSERT INTO people 
VALUES (1, 'Dave', 'Badger', 'Mr', date'1960-05-01')
```
```
1 row(s) inserted.
```
Statement 5
This statement inserts a row into the PEOPLE table. However, the statement fails to execute successfully because no value was provided for the BIRTH_DATE column.
Insert a row into the PEOPLE table
```
INSERT INTO people 
VALUES (2, 'Simon', 'Fox', 'Mr')
```
```
ORA-00947: not enough values 
```
More Details: https://docs.oracle.com/error-help/db/ora-00947
Statement 6
This statement inserts a row into the PEOPLE table. Although no value has been provided for the BIRTH_DATE column, the statement succeeds because the INSERT statement is followed by a list of columns into which values are being inserted.
Insert a row into the PEOPLE table
```
INSERT INTO people (person_id, given_name, family_name, title) 
VALUES (2, 'Simon', 'Fox', 'Mr')
```
```
1 row(s) inserted.
```
Statement 7
This statement inserts a row into the PEOPLE table. The value for the TITLE column is obtained by selecting a static value from the DUAL table.
Insert a row into the PEOPLE table
```
INSERT INTO people (person_id, given_name, family_name, title) 
VALUES (3, 'Dave', 'Frog', (SELECT 'Mr' FROM dual))
```
```
1 row(s) inserted.
```

Statement 8

This statement uses a select query to insert multiple rows into the PEOPLE table.

Insert multiple rows into the PEOPLE table

INSERT INTO people (person_id, given_name, family_name, title) 
  WITH names AS ( 
    SELECT 4, 'Ruth',     'Fox',      'Mrs'    FROM dual UNION ALL 
    SELECT 5, 'Isabelle', 'Squirrel', 'Miss'   FROM dual UNION ALL 
    SELECT 6, 'Justin',   'Frog',     'Master' FROM dual UNION ALL 
    SELECT 7, 'Lisa',     'Owl',      'Dr'     FROM dual 
  ) 
  SELECT * FROM names

4 row(s) inserted.

Statement 9
This statement rolls back the operations that inserted rows into the PEOPLE table.
Rollback previous DML operations
```
ROLLBACK
```
```
Statement processed.
```
Statement 10
```
SELECT * FROM people
```
```
no data found
```

Statement 11

This statement uses a select query with a WHERE condition to insert multiple rows into the PEOPLE table.

Insert multiple rows into the PEOPLE table

INSERT INTO people (person_id, given_name, family_name, title) 
  WITH names AS ( 
    SELECT 4, 'Ruth',     'Fox' family_name,      'Mrs'    FROM dual UNION ALL 
    SELECT 5, 'Isabelle', 'Squirrel' family_name, 'Miss'   FROM dual UNION ALL 
    SELECT 6, 'Justin',   'Frog' family_name,     'Master' FROM dual UNION ALL 
    SELECT 7, 'Lisa',     'Owl' family_name,      'Dr'     FROM dual 
  ) 
  SELECT * FROM names 
  WHERE  family_name LIKE 'F%'

2 row(s) inserted.

Statement 12
This statement rolls back the INSERT operation on the PEOPLE table.
Rollback insert operation on PEOPLE table
```
ROLLBACK
```
```
Statement processed.
```

Statement 13

This statement uses an INSERT ALL statement to insert multiple rows into the PEOPLE, PATIENTS, and STAFF tables. For each row that is inserted, the columns values are assigned based on the corresponding column names in the select query.

Insert multiple rows into the PEOPLE, PATIENTS, and STAFF tables

INSERT ALL 
  /* Every one is a person */ 
  INTO people (person_id, given_name, family_name, title) 
    VALUES (id, given_name, family_name, title) 
  INTO patients (patient_id, last_admission_date) 
    VALUES (id, admission_date) 
  INTO staff (staff_id, hired_date) 
    VALUES (id, hired_date) 
  WITH names AS ( 
    SELECT 4 id, 'Ruth' given_name, 'Fox' family_name, 'Mrs' title, 
           NULL hired_date, DATE'2009-12-31' admission_date 
    FROM   dual UNION ALL 
    SELECT 5 id, 'Isabelle' given_name, 'Squirrel' family_name, 'Miss' title , 
           NULL hired_date, DATE'2014-01-01' admission_date 
    FROM   dual UNION ALL 
    SELECT 6 id, 'Justin' given_name, 'Frog' family_name, 'Master' title, 
           NULL hired_date, DATE'2015-04-22' admission_date 
    FROM   dual UNION ALL 
    SELECT 7 id, 'Lisa' given_name, 'Owl' family_name, 'Dr' title, 
           DATE'2015-01-01' hired_date, NULL admission_date 
    FROM   dual 
  ) 
  SELECT * FROM names

12 row(s) inserted.

Statement 14
This statement rolls back the INSERT operation on the PEOPLE table.
Rollback insert operation on PEOPLE table
```
ROLLBACK
```
```
Statement processed.
```

Statement 15

This statement uses the INSERT ALL statement to conditionally insert multiple rows into the PEOPLE, PATIENTS, and STAFF tables. All the rows specified are inserted into the PEOPLE table because everyone is a person. Only rows for which the ADMISSION_DATE column is not null are patients and rows that satisfy this condition are inserted into the PATIENTS table. Only rows where the HIRE_DATE column is a non-null value are staff and therefore only rows that satisfy this condition are inserted into the STAFF table.

Conditionally insert multiple rows into the PEOPLE, PATIENTS, and STAFF tables

INSERT ALL 
  /* Everyone is a person, so insert all rows into people */ 
  WHEN 1=1 THEN 
    INTO people (person_id, given_name, family_name, title) 
    VALUES (id, given_name, family_name, title) 
  /* Only people with an admission date are patients */ 
  WHEN admission_date IS NOT NULL THEN 
    INTO patients (patient_id, last_admission_date) 
    VALUES (id, admission_date) 
  /* Only people with a hired date are staff */ 
  WHEN hired_date IS NOT NULL THEN 
    INTO staff (staff_id, hired_date) 
    VALUES (id, hired_date) 
  WITH names AS ( 
    SELECT 4 id, 'Ruth' given_name, 'Fox' family_name, 'Mrs' title, 
           NULL hired_date, DATE'2009-12-31' admission_date 
    FROM   dual UNION ALL 
    SELECT 5 id, 'Isabelle' given_name, 'Squirrel' family_name, 'Miss' title , 
           NULL hired_date, DATE'2014-01-01' admission_date 
    FROM   dual UNION ALL 
    SELECT 6 id, 'Justin' given_name, 'Frog' family_name, 'Master' title, 
           NULL hired_date, DATE'2015-04-22' admission_date 
    FROM   dual UNION ALL 
    SELECT 7 id, 'Lisa' given_name, 'Owl' family_name, 'Dr' title, 
           DATE'2015-01-01' hired_date, NULL admission_date 
    FROM   dual 
  ) 
  SELECT * FROM names

8 row(s) inserted.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database