Document Type | Technical Information
Category | Administration
Document Number | TADTI085
Overview
This guide provides an example of using the Conditional INSERT ALL statement in Tibero to insert data into multiple tables simultaneously with a single query.
Conditional INSERT ALL
Using Conditional INSERT ALL, only rows that meet specific conditions can be inserted into the desired tables.
The result rows from a subquery are checked in the WHEN โฆ THEN clause, and INSERT operations are performed into the tables specified in the clauses that meet the conditions.
-- Conditional INSERT ALL Syntax
INSERT ALL
WHEN condition 1 THEN
INTO [table 1] VALUES (column 1, column 2, ...)
WHEN condition 2 THEN
INTO [table 2] VALUES (column 1, column 2, ...)
........
ELSE
INTO [table 3] VALUES (column 1, column 2, ...)
Subquery;Method
Below is an example where different salary increase rates are applied by department and inserted into each respective table.
1. Creating test tables
-- Create test tables emp_dept10, emp_dept20, emp_dept30. DROP TABLE emp_dept10; DROP TABLE emp_dept20; DROP TABLE emp_dept30; CREATE TABLE emp_dept10 AS SELECT deptno, empno, ename, job, sal FROM emp WHERE 1=2; CREATE TABLE emp_dept20 AS SELECT deptno, empno, ename, job, sal FROM emp WHERE 1=2; CREATE TABLE emp_dept30 AS SELECT deptno, empno, ename, job, sal FROM emp WHERE 1=2;
2. Verify table creation
-- Verify that the tables were created successfully. SELECT * FROM emp_dept10; SELECT * FROM emp_dept20; SELECT * FROM emp_dept30;
3. INSERT based on department salary increase rates
-- Insert into each table with different salary increase rates by department.
-- deptno = 10 โ 10% increase, insert into emp_dept10
-- deptno = 20 โ 20% increase, insert into emp_dept20
-- deptno = 30 โ 30% increase, insert into emp_dept30
INSERT ALL
WHEN deptno = 10 THEN
INTO emp_dept10
VALUES (deptno, empno, ename, job, ROUND(sal * 1.1))
WHEN deptno = 20 THEN
INTO emp_dept20
VALUES (deptno, empno, ename, job, ROUND(sal * 1.2))
WHEN deptno = 30 THEN
INTO emp_dept30
VALUES (deptno, empno, ename, job, ROUND(sal * 1.3))
SELECT deptno, empno, ename, job, sal
FROM emp;4. Verify results
-- Check that data was inserted correctly into the tables.
SELECT d.deptno, d.sal "10%_UP_SALARY", e.sal "ORIGINAL_SALARY"
FROM emp_dept10 d, emp e
WHERE d.empno = e.empno;
-- Sample result
DEPTNO 10%_UP_SALARY ORIGINAL_SALARY
-------- -------------- ----------------
10 2695 2450
10 5500 5000
10 1430 1300
3 rows selected.
SELECT d.deptno, d.sal "20%_UP_SALARY", e.sal "ORIGINAL_SALARY"
FROM emp_dept20 d, emp e
WHERE d.empno = e.empno;
-- Sample result
DEPTNO 20%_UP_SALARY ORIGINAL_SALARY
-------- -------------- ----------------
20 960 800
20 3570 2975
20 3600 3000
20 1320 1100
20 3600 3000
5 rows selected.
SELECT d.deptno, d.sal "30%_UP_SALARY", e.sal "ORIGINAL_SALARY"
FROM emp_dept30 d, emp e
WHERE d.empno = e.empno;
-- Sample result
DEPTNO 30%_UP_SALARY ORIGINAL_SALARY
-------- -------------- ----------------
30 2080 1600
30 1625 1250
30 1625 1250
30 3705 2850
30 1950 1500
30 1235 950
6 rows selected.
SQL
Below is an example where different columns are used for the conditions to insert into each table.
The employee 7900 JAMES satisfies both conditions; since he meets the first condition, he is inserted into the emp_empno table, and as he also meets the second condition, he is also inserted into the emp_sal table.
1. Creating test tables
-- Create test tables emp_empno and emp_sal. DROP TABLE emp_empno; DROP TABLE emp_sal; CREATE TABLE emp_empno AS SELECT empno, ename, job, sal, deptno FROM emp WHERE 1=2; CREATE TABLE emp_sal AS SELECT empno, ename, job, sal, deptno FROM emp WHERE 1=2;
2. Verify table creation
-- Verify that the tables were created successfully. SELECT * FROM emp_empno; SELECT * FROM emp_sal;
3. Perform INSERT ALL based on conditions
-- Employees with empno >= 7876 are inserted into emp_empno
-- Employees with sal <= 1000 are inserted into emp_sal
INSERT ALL
WHEN empno >= 7876 THEN
INTO emp_empno
VALUES (empno, ename, job, sal, deptno)
WHEN sal <= 1000 THEN
INTO emp_sal
VALUES (empno, ename, job, sal, deptno)
SELECT empno, ename, job, sal, deptno
FROM emp;4. Verify results
SELECT * FROM emp_empno; EMPNO ENAME JOB SAL DEPTNO ------- ------ --------- ---- ------- 7876 ADAMS CLERK 1100 20 7900 JAMES CLERK 950 30 7902 FORD ANALYST 3000 20 7934 MILLER CLERK 1300 10 4 rows selected. SELECT * FROM emp_sal; EMPNO ENAME JOB SAL DEPTNO ------- ------ ------- ---- ------- 7369 SMITH CLERK 800 20 7900 JAMES CLERK 950 30 2 rows selected.