Multi-table insert : Oracle 9i or higher
1. Conditional Multi-table insert :
INSERT ALL
WHEN (period=6) THEN
INTO course_6 (course_name, period) values(course_name, period)
WHEN (period=3) THEN
INTO course_3 (course_name, period) values(course_name, period)
ELSE
INTO course_other (course_name, period) values(course_name, period)
SELECT course_name, period FROM course;
What does the above query do ?
1. Insert courses for period 6 into the table named COURSE_6.
2. Insert courses for period 3 into the table named COURSE_3.
3. Insert all other courses into the table named COURSE_OTHER
4. The ALL keyword causes Oracle to check each row returned by the SELECT query against each WHEN clause. If a row satisfies more than one WHEN clause, it is inserted into more than one table. Use the FIRST keyword to limit inserts to only the first matching WHEN clause.
2. Unconditional Multi-table insert :
INSERT ALL
INTO courses_taken (course_name) values (course_name)
INTO students_registered (student_name) values (student_name)
SELECT course_name, student_name
FROM enrollment;
- posted by Saravanan Jayachandran @ 9:21 PM Permanent Link