MULTI INSERT

Description

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

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 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;