DML (Multiple ways to Insert Rows into different tables)

CREATE TABLE person_details ( 

  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 

);

CREATE TABLE students ( 

  student_id          INTEGER NOT NULL PRIMARY KEY REFERENCES person_details (person_id), 

  admission_date DATE 

);

CREATE TABLE teachers ( 

  teachers_id   INTEGER NOT NULL PRIMARY KEY REFERENCES person_details (person_id), 

  hired_date DATE 

);

SELECT * FROM person_details;

SELECT * FROM students;

SELECT * FROM teachers;

INSERT INTO person_details 

VALUES (1, 'Dave', 'Badger', 'Mr', DATE'1960-05-01');

INSERT INTO person_details (person_id, given_name, family_name, title) 

VALUES (2, 'Simon', 'Fox', 'Mr');

INSERT INTO person_details (person_id, given_name, family_name, title) 

VALUES (3, 'Dave', 'Frog', (SELECT 'Mr' FROM dual));

INSERT INTO person_details (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;

SELECT * FROM person_details;

ROLLBACK;

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

INSERT INTO person_details (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%';

SELECT * FROM person_details;

ROLLBACK;

-- Insert multiple rows into the person_details, students, and teachers tables

-- This statement uses an INSERT ALL statement to insert multiple rows into the person_details, 
-- students, and teachers tables.

-- For each row that is inserted, the columns values are assigned based on the corresponding column 
-- names in the select query.

INSERT ALL 

  INTO person_details (person_id, given_name, family_name, title) 

    VALUES (id, given_name, family_name, title) 

  INTO students (student_id, admission_date) 

    VALUES (id, admission_date) 

  INTO teachers (teachers_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;

SELECT * FROM person_details;

SELECT * FROM students;

SELECT * FROM teachers;

ROLLBACK;

-- Conditionally insert multiple rows into the person_details, students, and teachers tables

-- This statement uses the INSERT ALL statement to conditionally insert multiple rows into the 
-- person_details, students, and teachers tables. 

-- All the rows specified are inserted into the person_details table because everyone is a person.

-- Only rows for which the ADMISSION_DATE column is not null are students and rows that satisfy -- this condition are inserted into the students table.

-- Only rows where the HIRE_DATE column is a non-null value are teachers and therefore only rows -- that satisfy this condition are inserted into the teachers table.

INSERT ALL 

  /* Everyone is a person, so insert all rows into person_details */ 

  WHEN 1=1 THEN 

    INTO person_details (person_id, given_name, family_name, title) 

    VALUES (id, given_name, family_name, title) 

  /* Only person_details with an admission date are students */ 

  WHEN admission_date IS NOT NULL THEN 

    INTO students (student_id, admission_date) 

    VALUES (id, admission_date) 

  /* Only person_details with a hired date are teachers */ 

  WHEN hired_date IS NOT NULL THEN 

    INTO teachers (teachers_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;

SELECT * FROM person_details;

SELECT * FROM students;

SELECT * FROM teachers;

ROLLBACK;

I hope you all have enjoyed reading this article. Comments are welcome....