MaxCompute:MULTI INSERT

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 MaxCompute permissions.

Description

This topic describes how to execute a MULTI INSERT statement to insert data into different destination tables or partitions of a table in MaxCompute.

Limits

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 multiple partitions, such as 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 statement:

  -- Create a 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: Specify the same partition 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;