Skip to main content

Search

Welcome to Tibero GTS!

Tibero Multiple Table Insert Example (1)

Document TypeㅣTechnical Information

CategoryㅣAdministration

Document NumberㅣTADTI084

 

Overview

This guides an example of inserting data into multiple tables simultaneously in a single query using the Unconditional INSERT ALL syntax in Tibero.
 

Unconditional INSERT ALL

Unconditional INSERT ALL allows you to insert data into multiple tables with a single INSERT statement.
Here, the number and data types of columns specified in the INTO clause and VALUES clause must be the same.
-- Unconditional INSERT ALL Syntax
INSERT ALL
    INTO tab1 VALUES(col1, col2, col3)
    INTO tab2 VALUES(col1, col4, col5)
SELECT col1, col2, col3, col4, col5 FROM tab

 

 

Method

The example below demonstrates inserting data from the emp table into the emp_ename and emp_deptno tables simultaneously using INSERT ALL.
 
1. Practice Environment
-- Practiced using the emp table in the tibero account.

tbsql tibero/tmax

2. Creating Test Tables
-- Create test tables emp_ename and emp_deptno.
-- Create emp_ename table with empno, ename, sal columns
CREATE TABLE emp_ename
         AS
SELECT empno, ename, sal
     FROM emp
  WHERE 1=2;

-- Create emp_deptno table with empno, deptno, job columns
CREATE TABLE emp_deptno
         AS
SELECT empno, deptno, job
     FROM emp
  WHERE 1=2;

3. Confirm Table Creation
-- Verify that the tables were created successfully.

SELECT * FROM emp_ename;
SELECT * FROM emp_deptno;

4. Execute INSERT ALL
-- Use INSERT ALL to insert into emp_ename and emp_deptno tables.
INSERT ALL
    INTO emp_ename VALUES (empno, ename, sal)
    INTO emp_deptno (empno, deptno, job) VALUES (empno, deptno, job)
SELECT empno, ename, sal, deptno, job
    FROM emp
WHERE sal >= 2900;

5. Verify Results
-- Check if data was inserted correctly.
-- 4 records satisfying the condition sal >= 2900 are inserted into emp_ename and emp_deptno tables.
SQL> SELECT * FROM emp_ename;

EMPNO   ENAME            SAL
-------  -----------   -----
   7566  JONES          2975
   7788  SCOTT          3000
   7839  KING           5000
   7902  FORD           3000

4 rows selected.

SQL> SELECT * FROM emp_deptno; 

EMPNO    DEPTNO   JOB
-------  -------  ---------------
   7566       20  MANAGER
   7788       20  ANALYST
   7839       10  PRESIDENT
   7902       20  ANALYST

4 rows selected.
 
 
 

Related to