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.