MaxCompute SQL allows you to insert data into different destination tables or partitions at the same time by using a MULTI INSERT statement. If you execute a MULTI INSERT statement, multiple INSERT INTO or INSERT OVERWRITE operations are performed at the same time.

You can execute the INSERT INTO or INSERT OVERWRITE statement on the following platforms:

Prerequisites

You have the ALTER permission on the destination tables and the DESCRIBE permission on metadata in the source table. For more information about how to grant the permissions, see Authorize users.

Description

To use MaxCompute SQL to process data, you can execute a MULTI INSERT statement to insert data into different destination tables or partitions at the same time.

Limits

When you execute a MULTI INSERT statement, take note of the following limits:
  • A single MULTI INSERT statement can contain up to 255 INSERT operations. If the number of INSERT operations exceeds 255, a syntax error is returned.
  • In a MULTI INSERT statement, you cannot specify the same destination partition in a partitioned table for multiple INSERT operations.
  • In a MULTI INSERT statement, you cannot specify the same non-partitioned table for multiple INSERT operations.

Syntax

from <from_statement>
insert overwrite | into table <table_name1> [partition (<pt_spec1>)]
<select_statement1>
insert overwrite | into table <table_name2> [partition (<pt_spec2>)]
<select_statement2>
...;
  • from_statement: required. The FROM clause that specifies the data source. For example, you can specify the name of a source table in this clause.
  • table_name: required. The names of the tables into which you want to insert data.
  • pt_spec: optional. The partitions into which you want to insert data. Only constants are allowed. Expressions, such as functions, are not allowed. The value of this parameter is in the (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...) format. If you want to insert data into two partitions that are specified by pt_spec1 and pt_spec2, the partition information of pt_spec1 and pt_spec2 must be different.
  • select_statement: required. The SELECT clause that is used to query the data that you want to insert into the destination tables or partitions from the source table.

Examples

  • Example 1: Insert data from the sale_detail table into the specified partitions of the sale_detail_multi table. The partitions store the sales records for the years 2010 and 2011 in the Chinese mainland. Sample statements:
    -- Create a destination table named sale_detail_multi. 
    create table sale_detail_multi like sale_detail;
    
    -- Enable a full table scan only for the current session. -- Insert data from the sale_detail table into the sale_detail_multi table. 
    set odps.sql.allow.fullscan=true; 
    from sale_detail
    insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
    select shop_name, customer_id, total_price
    insert overwrite table sale_detail_multi partition (sale_date='2011', region='china' )
    select shop_name, customer_id, total_price;
    
    -- Enable a full table scan only for the current session. Execute the SELECT statement to query the data in the sale_detail_multi table. 
    set odps.sql.allow.fullscan=true;
    select * from sale_detail_multi;
    
    -- The following result is returned: 
    +------------+-------------+-------------+------------+------------+
    | shop_name  | customer_id | total_price | sale_date  | region     |
    +------------+-------------+-------------+------------+------------+
    | s1         | c1          | 100.1       | 2010       | china      |
    | s2         | c2          | 100.2       | 2010       | china      |
    | s3         | c3          | 100.3       | 2010       | china      |
    | s1         | c1          | 100.1       | 2011       | china      |
    | s2         | c2          | 100.2       | 2011       | china      |
    | s3         | c3          | 100.3       | 2011       | china      |
    +------------+-------------+-------------+------------+------------+
  • Example 2: If the same partition is specified for multiple INSERT operations in a single MULTI INSERT statement, an error is returned. Sample statement of incorrect usage:
    from sale_detail
    insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
    select shop_name, customer_id, total_price
    insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
    select shop_name, customer_id, total_price;