INSERT ALL and INSERT FIRST in GBase 8s: Usage and Examples

When you need to insert the same batch of data into multiple tables efficiently and consistently, GBASE's GBase 8s offers INSERT ALL and INSERT FIRST. They read the source table only once, eliminating the risk of data inconsistency that comes with running separate INSERT statements.

Syntax Overview

• WHEN…THEN…: Can appear multiple times. INSERT FIRST acts on the first true WHEN and then stops; INSERT ALL continues evaluating and executing all subsequent true conditions.
• Requires Oracle compatibility mode: SET ENVIRONMENT SQLMODE 'ORACLE';

Sample Data Setup

CREATE TABLE testab(id INT, name VARCHAR(20), sex VARCHAR(20), age INT);
INSERT INTO testab VALUES(101, 'lisi', 'female', 18);
INSERT INTO testab VALUES(102, 'lisi', 'female', 18);
INSERT INTO testab VALUES(103, 'xiaowu', 'male', 19);

CREATE TABLE tab1 AS SELECT * FROM testab WHERE 1 = 2;
CREATE TABLE tab2 AS SELECT * FROM testab WHERE 1 = 2;

Unconditional Insert (INSERT ALL)

Without WHEN, every row is inserted into each listed table.

INSERT ALL
    INTO tab1 VALUES(id, name, sex, age)
    INTO tab2 VALUES(id, name, sex, age)
SELECT * FROM testab;

The 3 rows from testab land in both tab1 and tab2, returning 6 inserted rows. To insert a single static row, use SELECT … FROM dual:

INSERT ALL
    INTO tab1 VALUES(111, 'xiaowang', 'female', 18)
    INTO tab2 VALUES(111, 'xiaowang', 'female', 18)
SELECT 1 FROM dual;

Conditional Inserts

INSERT ALL WHEN

Executes every matching WHEN; unhandled rows can be caught by ELSE.

INSERT ALL
    WHEN id = 101 THEN
        INTO tab1 VALUES(id, name, sex, age)
    WHEN id = 102 THEN
        INTO tab2 VALUES(id, name, sex, age)
    ELSE
        INTO tab1 VALUES(id, name, sex, age)
SELECT id, name, sex, age FROM testab;

• id=101 → tab1
• id=102 → tab2
• id=103 falls to ELSE → tab1

INSERT FIRST WHEN

Only the first matching WHEN executes; subsequent conditions are skipped.

INSERT FIRST
    WHEN id = 101 THEN
        INTO tab1 VALUES(id, name, sex, age)
    WHEN id = 101 THEN
        INTO tab2 VALUES(id, name, sex, age)
SELECT id, name, sex, age FROM testab;

Here, only id=101 goes into tab1; tab2 remains empty.

Row-to-Column Unpivot

Convert a single row's column values into multiple rows.

CREATE TABLE testab1(id INT, name VARCHAR(20), wagemon FLOAT, wagetue FLOAT, wagewed FLOAT);
INSERT INTO testab1 VALUES(111, 'xiaowang', 1.1, 1.2, 1.3);

CREATE TABLE testmp(id INT, name VARCHAR(20), wage FLOAT);

INSERT ALL
    INTO testmp VALUES(id, name, wagemon)
    INTO testmp VALUES(id, name, wagetue)
    INTO testmp VALUES(id, name, wagewed)
SELECT * FROM testab1;

testmp receives 3 rows, with the wage column holding wagemon, wagetue, and wagewed.

Restrictions

• Only works on tables — not views, materialized views, or remote tables.
• Cannot use table collection expressions.
• Target columns must not exceed 999.
• Cannot run in parallel on RAC, on an index‑organized table, or when a BITMAP index exists.
• Plan stability is not supported.
• Sequences cannot appear in the subquery.

Mastering INSERT ALL and INSERT FIRST helps you build cleaner, more consistent multi‑table data flows in your gbase database environment.