Skip to main content

Search

Welcome to Tibero GTS!

Tibero Multi-Table Insert Example (3)

Document Type | Technical Information

Category | Administration

Document Number | TADTI086

 

Overview

This guides an example of inserting data into multiple tables simultaneously with a single query using the Conditional INSERT FIRST statement in Tibero.
 

Conditional INSERT FIRST

Using Conditional INSERT FIRST allows you to insert rows that meet specific conditions into the desired tables.
Caution
Only the first condition that is satisfied will be executed.
 
The result rows from the subquery are checked in the WHEN โ€ฆ THEN clauses, and the INSERT operation is performed into the table specified in the clause that matches the condition.
-- Conditional INSERT FIRST Syntax
INSERT FIRST
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

We modify the previous example of Conditional INSERT ALL to use INSERT FIRST.
 
As shown below, once the first condition is met, the subsequent conditions will not be executed. Therefore, the employee 7900 JAMES, who satisfies both conditions, will not be inserted into the emp_empno table because the second condition is not executed.
 
1. Create 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. Confirm Table Creation
-- Verify that the tables were created successfully.
SELECT * FROM emp_empno;
SELECT * FROM emp_sal;

3. Conditional Insert Using INSERT FIRST
-- Insert employees with empno >= 7876 into emp_empno table
-- Insert employees with sal <= 1000 into emp_sal table

INSERT FIRST
    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
-- Check that data (empno >= 7876) was correctly inserted into emp_empno table.

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.


-- Check that data (sal <= 1000) was correctly inserted into emp_sal table.

SELECT * FROM emp_sal;

 EMPNO   ENAME   JOB     SAL   DEPTNO
-------  ------  ------  ----  -------
  7369   SMITH   CLERK    800      20

1 row selected.

 
 

Related to