Help Center/ GaussDB/ Developer Guide(Distributed_V2.0-8.x)/ SQL Reference/ SQL Syntax/ I/ INSERT ALL
Updated on 2025-05-29 GMT+08:00
View PDF
Share

INSERT ALL

Description

Inserts one or multiple data records into multiple tables.

Precautions

  • You must have the INSERT permission on a table to insert data into it. If a user is granted the INSERT ANY TABLE permission, the user has the USAGE permission on all schemas except system schemas and the INSERT permission on tables in these schemas.
  • If you use the query clause to insert rows from a query, you need to have the SELECT permission on any table or column used in the query.
  • Data cannot be written directly to a generated column. In the INSERT statement, values cannot be specified for generated columns, but the keyword DEFAULT can be specified.
  • This syntax can be executed only in the ORA-compatible mode.

Syntax

INSERT [/*+ plan_hint */] [ ALL | FIRST ]
    { [ WHEN condition THEN ]
    { INTO table_name
    [ { [alias_name] [ ( column_name [, ...] ) ] } | { [partition_clause] [ AS alias ] 
    [ ( column_name [, ...] ) ] } ]
    [ VALUES {( { expression | DEFAULT } [, ...] ) } ] }[, ...] }[, ...]
    { subquery };

Parameters

  • plan_hint clause

    Follows the INSERT keyword in the /*+ */ format. Only the syntax format is adapted, and specific functions are not supported.

  • ALL/FIRST

    The keyword cannot be omitted and can only be ALL, if a conditional clause (WHEN condition THEN) is not used. The keyword can be omitted and is ALL by default if a conditional clause is used. In this case, the meanings of ALL and FIRST are as follows:

    • ALL: Run through all branches of matching conditions and insert data into the corresponding table as long as the conditions are met.
    • FIRST: After finding the first branch that meets the conditions, insert the data into the table corresponding to the branch, and the check for remaining branches stops.
  • WHEN condition THEN

    Specifies the condition judgment when inserting data into a table. The insertion is performed only when the condition is met. For conditions, refer to the columns in the subquery.

  • table_name

    Specifies the name of the target table where data will be inserted.

    Value range: an existing table name

  • alias_name

    Specifies the table alias when the INSERT statement is used without AS alias.

    1. When the INTO clause is used without AS alias, the table alias cannot be a keyword (such as SELECT and VALUE) or an expression. The alias must comply with the Identifier Naming Conventions.
    2. When the INTO clause is used without AS alias, the table alias cannot be in the INTO table_name alias_name(alias_name.col1, ...,alias_name.coln) VALUES(xxx); format.
    3. When the INTO clause is used without AS alias, data cannot be inserted into the specified partition.
  • partition_clause

    Inserts data to a specified partition.

    PARTITION { ( partition_name ) | FOR ( partition_value [, ...] ) } |
SUBPARTITION { ( subpartition_name ) | FOR ( subpartition_value [, ...] ) }

    For details about the keywords, see SELECT.

    If the value of the VALUE clause is inconsistent with that of the specified partition, an exception is displayed.

  • column_name

    Specifies the name of a column in a table.

    • The column name can be qualified with a subcolumn name or array index, if needed.
    • Each column not present in the column list will be filled with a default value, either its declared default value or NULL if there is none. Inserting data into only some columns of a composite type leaves the other columns NULL.
    • The target column names column_name can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.
    • The target columns are the first N column names, if there are only N columns provided by the VALUE clause and QUERY.
    • The values provided by the VALUE clause and QUERY are joined with the corresponding columns from left to right in the table.

    Value range: an existing column

  • VALUES

    It cannot be followed by multiple rows.

  • expression

    Specifies an expression or a value to assign to the corresponding column.

    • If single-quotation marks (') are inserted into a column, the single-quotation marks need to be used for escape.
    • If the expression for any column is not of the correct data type, automatic type conversion will be attempted. If the attempt fails, data insertion fails, and the system returns an error message.
    • Aggregate functions are not supported.
    • Subqueries are not supported.
  • DEFAULT

    Specifies the default value of a column. The value is NULL if no specified default value has been assigned to it.

  • subquery

    Specifies a query statement (SELECT statement) that uses query results as the inserted data and cannot be omitted. If there are no subqueries, use SELECT * FROM DUAL.

    If a subquery contains a table alias, the alias cannot be referenced in the CONDITION and INTO clauses.

Examples

  • Inserting data to multiple tables without conditions
    Example: 
    -- Create an ORA-compatible database.
gaussdb=# CREATE DATABASE insertall_test_db DBCOMPATIBILITY 'ORA';

-- Switch to an ORA-compatible database.
gaussdb=# \c insertall_test_db;

-- Create tables.
insertall_test_db=# CREATE TABLE insert_all_test0(c1 INT, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test1(c1 INT, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test2(c1 INT, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test3(c1 INT, c2 VARCHAR2(20));

-- Construct data.
insertall_test_db=# INSERT INTO insert_all_test0 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

-- Insert data to the tables.
insertall_test_db=# INSERT ALL
insertall_test_db-# INTO insert_all_test1
insertall_test_db-# INTO insert_all_test1 VALUES(1,'a')
insertall_test_db-# INTO insert_all_test1(c1,c2)
insertall_test_db-# INTO insert_all_test1(c1) VALUES(1)
insertall_test_db-# INTO insert_all_test1(c2) VALUES('aaa')
insertall_test_db-# INTO insert_all_test2(c2) VALUES(c2)
insertall_test_db-# INTO insert_all_test2(c2) VALUES(c2||'*')
insertall_test_db-# INTO insert_all_test3(c1,c2) VALUES(1,'a')
insertall_test_db-# INTO insert_all_test3(c1,c2) VALUES(1,c2)
insertall_test_db-# INTO insert_all_test3(c1,c2) VALUES(c1,c2)
insertall_test_db-# INTO insert_all_test3(c2,c1) VALUES('***',666)
insertall_test_db-# SELECT * FROM insert_all_test0;

-- Query data.
insertall_test_db=# SELECT * FROM insert_all_test1 ORDER BY c1,c2;
 c1 | c2  
----+-----
  1 | a
  1 | a
  1 | a
  1 | a
  1 | a
  1 | a
  1 | a
  1 | 
  1 | 
  1 | 
  1 | 
  1 | 
  2 | b
  2 | b
  3 | c
  3 | c
  4 | d
  4 | d
  5 | e
  5 | e
    | aaa
    | aaa
    | aaa
    | aaa
    | aaa
(25 rows)

insertall_test_db=# SELECT * FROM insert_all_test2 ORDER BY c1,c2;
 c1 | c2 
----+----
    | a
    | a*
    | b
    | b*
    | c
    | c*
    | d
    | d*
    | e
    | e*
(10 rows)

insertall_test_db=# SELECT * FROM insert_all_test3 ORDER BY c1,c2;
 c1  | c2  
-----+-----
   1 | a
   1 | a
   1 | a
   1 | a
   1 | a
   1 | a
   1 | a
   1 | b
   1 | c
   1 | d
   1 | e
   2 | b
   3 | c
   4 | d
   5 | e
 666 | ***
 666 | ***
 666 | ***
 666 | ***
 666 | ***
(20 rows)

-- Drop the tables.
insertall_test_db=# DROP TABLE insert_all_test0;
insertall_test_db=# DROP TABLE insert_all_test1;
insertall_test_db=# DROP TABLE insert_all_test2;
insertall_test_db=# DROP TABLE insert_all_test3;

insertall_test_db=# \c postgres;
gaussdb=# DROP DATABASE insertall_test_db;
  • Inserting data to multiple tables with conditions
    Example 1: INSERT FIRST 
    -- Create an ORA-compatible database.
gaussdb=# CREATE DATABASE insertall_test_db DBCOMPATIBILITY 'ORA';

-- Switch to an ORA-compatible database.
gaussdb=# \c insertall_test_db;

-- Create tables.
insertall_test_db=# CREATE TABLE insert_all_test0(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test1(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test2(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test3(c1 int, c2 VARCHAR2(20));

-- Construct data.
insertall_test_db=# INSERT INTO insert_all_test0 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');
-- Insert data to the tables.
insertall_test_db=# INSERT FIRST
insertall_test_db-# WHEN c1 < 4 THEN
insertall_test_db-# INTO insert_all_test1
insertall_test_db-# WHEN c1 < 3 THEN
insertall_test_db-# INTO insert_all_test2
insertall_test_db-# ELSE
insertall_test_db-# INTO insert_all_test3
insertall_test_db-# SELECT * FROM insert_all_test0;

-- Query data.
insertall_test_db=# SELECT * FROM insert_all_test1 ORDER BY c1,c2;
 c1 | c2 
----+----
  1 | a
  2 | b
  3 | c
(3 rows)

insertall_test_db=# SELECT * FROM insert_all_test2 ORDER BY c1,c2;
 c1 | c2 
----+----
(0 rows)

insertall_test_db=# SELECT * FROM insert_all_test3 ORDER BY c1,c2;
 c1 | c2 
----+----
  4 | d
  5 | e
(2 rows)

-- Drop the tables.
insertall_test_db=# DROP TABLE insert_all_test0;
insertall_test_db=# DROP TABLE insert_all_test1;
insertall_test_db=# DROP TABLE insert_all_test2;
insertall_test_db=# DROP TABLE insert_all_test3;

insertall_test_db=# \c postgres;
gaussdb=# DROP DATABASE insertall_test_db;

    Example 2: INSERT ALL

    -- Create an ORA-compatible database.
gaussdb=# CREATE DATABASE insertall_test_db DBCOMPATIBILITY 'ORA';

-- Switch to an ORA-compatible database.
gaussdb=# \c insertall_test_db;

-- Create tables.
insertall_test_db=# CREATE TABLE insert_all_test0(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test1(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test2(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test3(c1 int, c2 VARCHAR2(20));

-- Construct data.
insertall_test_db=# INSERT INTO insert_all_test0 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

-- Insert data to the tables with ALL.
insertall_test_db=# INSERT ALL
insertall_test_db-# WHEN c1 < 4 THEN
insertall_test_db-# INTO insert_all_test1
insertall_test_db-# WHEN c1 < 3 THEN
insertall_test_db-# INTO insert_all_test2
insertall_test_db-# ELSE
insertall_test_db-# INTO insert_all_test3
insertall_test_db-# SELECT * FROM insert_all_test0;

-- Query data.
insertall_test_db=# SELECT * FROM insert_all_test1 ORDER BY c1,c2;
 c1 | c2 
----+----
  1 | a
  2 | b
  3 | c
(3 rows)

insertall_test_db=# SELECT * FROM insert_all_test2 ORDER BY c1,c2;
 c1 | c2 
----+----
  1 | a
  2 | b
(2 rows)

insertall_test_db=# SELECT * FROM insert_all_test3 ORDER BY c1,c2;
 c1 | c2 
----+----
  4 | d
  5 | e
(2 rows)

-- Drop data.
insertall_test_db=# TRUNCATE TABLE insert_all_test1;
insertall_test_db=# TRUNCATE TABLE insert_all_test2;
insertall_test_db=# TRUNCATE TABLE insert_all_test3;

-- Insert data to the tables without ALL.
insertall_test_db=# insert
insertall_test_db-# WHEN c1 < 4 THEN
insertall_test_db-# INTO insert_all_test1
insertall_test_db-# WHEN c1 < 3 THEN
insertall_test_db-# INTO insert_all_test2
insertall_test_db-# ELSE
insertall_test_db-# INTO insert_all_test3
insertall_test_db-# SELECT * FROM insert_all_test0;

-- Query data.
insertall_test_db=# SELECT * FROM insert_all_test1 ORDER BY c1,c2;
 c1 | c2 
----+----
  1 | a
  2 | b
  3 | c
(3 rows)

insertall_test_db=# SELECT * FROM insert_all_test2 ORDER BY c1,c2;
 c1 | c2 
----+----
  1 | a
  2 | b
(2 rows)

insertall_test_db=# SELECT * FROM insert_all_test3 ORDER BY c1,c2;
 c1 | c2 
----+----
  4 | d
  5 | e
(2 rows)

-- Drop the tables.
insertall_test_db=# DROP TABLE insert_all_test0;
insertall_test_db=# DROP TABLE insert_all_test1;
insertall_test_db=# DROP TABLE insert_all_test2;
insertall_test_db=# DROP TABLE insert_all_test3;

insertall_test_db=# \c postgres;
gaussdb=# DROP DATABASE insertall_test_db;
  • Support INSERT ALL in PL/SQL.

    Example:

    -- Create an ORA-compatible database.
gaussdb=# CREATE DATABASE insertall_test_db DBCOMPATIBILITY 'ORA';

-- Switch to an ORA-compatible database.
gaussdb=# \c insertall_test_db;

-- Create tables.
insertall_test_db=# CREATE TABLE insert_all_test0(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test1(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test2(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test3(c1 int, c2 VARCHAR2(20));

-- Construct data.
insertall_test_db=# INSERT INTO insert_all_test0 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

-- Insert data to the tables using PL/SQL.
insertall_test_db=# DECLARE
insertall_test_db-# var1 INT := 666;
insertall_test_db-# var2 VARCHAR2(20) := '***';
insertall_test_db-# BEGIN
insertall_test_db$# INSERT ALL
insertall_test_db$# INTO insert_all_test1 VALUES(var1,var2)
insertall_test_db$# INTO insert_all_test1(c1) VALUES(var1)
insertall_test_db$# INTO insert_all_test2(c2) VALUES(var2||'???')
insertall_test_db$# INTO insert_all_test3(c1,c2) VALUES(c1,c2)
insertall_test_db$# SELECT * FROM insert_all_test0;
insertall_test_db$# END;
insertall_test_db$# /

-- Query data.
insertall_test_db=# SELECT * FROM insert_all_test1 ORDER BY c1,c2;
 c1  | c2  
-----+-----
 666 | ***
 666 | ***
 666 | ***
 666 | ***
 666 | ***
 666 | 
 666 | 
 666 | 
 666 | 
 666 | 
(10 rows)

insertall_test_db=# SELECT * FROM insert_all_test2 ORDER BY c1,c2;
 c1 |   c2   
----+--------
    | ***???
    | ***???
    | ***???
    | ***???
    | ***???
(5 rows)

insertall_test_db=# SELECT * FROM insert_all_test3 order by c1,c2;
 c1 | c2 
----+----
  1 | a
  2 | b
  3 | c
  4 | d
  5 | e
(5 rows)

-- Drop the tables.
insertall_test_db=# DROP TABLE insert_all_test0;
insertall_test_db=# DROP TABLE insert_all_test1;
insertall_test_db=# DROP TABLE insert_all_test2;
insertall_test_db=# DROP TABLE insert_all_test3;

insertall_test_db=# \c postgres;
gaussdb=# DROP DATABASE insertall_test_db;
  • Exceptions

    Example:

    -- Create an ORA-compatible database.
gaussdb=# CREATE DATABASE insertall_test_db DBCOMPATIBILITY 'ORA';

-- Switch to an ORA-compatible database.
gaussdb=# \c insertall_test_db;

-- Create tables.
insertall_test_db=# CREATE TABLE insert_all_test0(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test1(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test2(c1 int, c2 VARCHAR2(20));
insertall_test_db=# CREATE TABLE insert_all_test3(c1 int, c2 VARCHAR2(20));

-- Construct data.
insertall_test_db=# INSERT INTO insert_all_test0 VALUES(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e');

-- Aggregate functions are not supported in VALUSES.
insertall_test_db=# INSERT ALL INTO insert_all_test1 VALUES(max(c1), 'a') SELECT * FROM insert_all_test0;
ERROR:  An aggregate function cannot be used in VALUES.
LINE 1: INSERT ALL INTO insert_all_test1 VALUES(max(c1), 'a') SELECT

-- Multiple rows cannot be added after VALUES.
insertall_test_db=# INSERT ALL INTO insert_all_test1 VALUES(1,'a'),(2,'b') SELECT * FROM insert_all_test0;
ERROR:  syntax error at or near ","
LINE 1: INSERT ALL INTO insert_all_test1 VALUES(1,'a'),(2,'b') selec...

-- The column in the WHEN condition does not exist in the subquery.
insertall_test_db=# INSERT ALL WHEN c1>1 THEN INTO insert_all_test1 VALUES(1,'a') SELECT c2 FROM insert_all_test0;
ERROR:  Column "c1" does not exist.
LINE 1: INSERT ALL WHEN c1>1 THEN INTO insert_all_test1 VALUES(1,'a'...
                        ^
HINT:  There is a column named "c1" in table "insert_all_test1", but it cannot be referenced from this part of the query.

-- Aliases cannot be set for subquery tables.
insertall_test_db=# INSERT ALL INTO insert_all_test1 VALUES(t1.c1, 'a') SELECT * FROM insert_all_test0 t1;
ERROR:  missing FROM-clause entry for table "t1" 
LINE 2: INSERT ALL INTO insert_all_test1 VALUES(t1.c1, 'a')

-- Data cannot be inserted into a view.
insertall_test_db=# CREATE VIEW v1 as SELECT * FROM insert_all_test1;
insertall_test_db=# INSERT ALL INTO v1 SELECT * FROM insert_all_test0;
ERROR:  Not allowed to insert into view.

-- Subqueries do not exist.
insertall_test_db=# INSERT ALL INTO insert_all_test1 VALUES(1, 'a');
ERROR:  syntax error at or near ";" 
LINE 2: INSERT ALL INTO insert_all_test1 VALUES(1, 'a');

-- Drop the tables.
insertall_test_db=# DROP TABLE insert_all_test0;
insertall_test_db=# DROP TABLE insert_all_test1;
insertall_test_db=# DROP TABLE insert_all_test2;
insertall_test_db=# DROP TABLE insert_all_test3;

insertall_test_db=# \c postgres;
gaussdb=# DROP DATABASE insertall_test_db;
Parent topic: I