MaxCompute SQL allows you to insert data into different destination tables or partitions by using INSERT INTO or INSERT OVERWRITE in an SQL statement.

Description

If you use MaxCompute SQL to process data, MULTI INSERT can be used to insert data into different destination tables or partitions.

Limits

When you execute MULTI INSERT, take note of the following limits:
  • A single MULTI INSERT statement can contain a maximum of 255 INSERT operations. If the number of INSERT operations exceeds 255, a syntax error is returned.
  • In a single MULTI INSERT statement, the same destination partition in a partitioned table cannot be specified in multiple INSERT operations.
  • In a single MULTI INSERT statement, the same non-partitioned table cannot be specified in 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 indicates the data source, such as a source table name.
  • table_name: required. The name of the destination table into which you want to insert data.
  • pt_spec: optional. The partition into which you want to insert data. Only constants are allowed. Expressions, such as functions, are not allowed. The value is in the format of (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). If you want to insert data into two partitions that are specified by pt_spec1 and pt_spec2, the partition information in pt_spec1 and pt_spec2 must be different.
  • select_statement: required. The SELECT clause that is used to query data that you want to insert into the destination table from the source table.

Examples

  • Example 1: Insert data from the sale_detail table into the sales records of years 2010 and 2011 in China of the sale_detail_multi table. Sample commands:
    -- 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 view 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 in multiple INSERT operations in a single MULTI INSERT statement, an error is returned. Example 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;