Skip to main content

Search

Welcome to Tibero GTS!

How to Perform Multi-Table Insert in Tibero

Document Type | Technical Information

Category | Administration

Document Number | TADTI083

 

Overview

This guide explains how to perform multi-table Insert operations in Tibero.
Note
Testing was conducted in the following environment.
  • [tibero@T1:/tibero]$ tbboot -v
  • Tibero 6 (DB 6.0 FS06_CS_1703) Linux stopbugs 2.6.9-89.ELsmp #1 SMP Mon Jun 22 12:31:33 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)
  • Patch files (none)
 
 

Method

How to Perform Insert

When performing INSERT into multiple tables with a single query, you can use the following methods.
  • Unconditional INSERT ALL : Executes all multiple INSERT statements. (โ€ป When inserting SELECT results into multiple tables, data types must match.)
  • Conditional INSERT ALL : Inserts rows that meet the WHEN condition into multiple tables.
  • Conditional INSERT FIRST : Performs INSERT only for the first WHEN condition that is satisfied. INSERTs for other conditions are not performed.
 

Creating Test Tables

DROP TABLE EMP;

CREATE TABLE TIBERO.EMP (
    EMPNO   NUMBER(4)      NOT NULL,
    ENAME   VARCHAR(10),
    JOB     VARCHAR(9),
    MGR     NUMBER(4),
    HIREDATE DATE,
    SAL     NUMBER(7,2),
    COMM    NUMBER(7,2),
    DEPTNO  NUMBER(2)
);

INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',    7902, TO_DATE('1980/12/17', 'YYYY/MM/DD'),  800,  NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN', 7698, TO_DATE('1981/02/20', 'YYYY/MM/DD'), 1600,  300,  30);
INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN', 7698, TO_DATE('1981/02/22', 'YYYY/MM/DD'), 1250,  500,  30);
INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',  7839, TO_DATE('1981/04/02', 'YYYY/MM/DD'), 2975,  NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981/09/28', 'YYYY/MM/DD'), 1250, 1400,  30);
INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',  7839, TO_DATE('1981/05/01', 'YYYY/MM/DD'), 2850,  NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',  7839, TO_DATE('1981/06/09', 'YYYY/MM/DD'), 2450,  NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',  7566, TO_DATE('1987/04/19', 'YYYY/MM/DD'), 3000,  NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL, TO_DATE('1981/11/17', 'YYYY/MM/DD'), 5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981/09/08', 'YYYY/MM/DD'), 1500,    0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',    7788, TO_DATE('1987/05/23', 'YYYY/MM/DD'), 1100,  NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',    7698, TO_DATE('1981/12/03', 'YYYY/MM/DD'),  950,  NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',  7566, TO_DATE('1981/12/03', 'YYYY/MM/DD'), 3000,  NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',    7782, TO_DATE('1982/01/23', 'YYYY/MM/DD'), 1300,  NULL, 10);

 

 

 

 

Related to