Use MULTI INSERT to simultaneously insert data into multiple tables or partitions - MaxCompute

Prerequisites

Before you begin, ensure that you have:

The ALTER permission on each destination table
The DESCRIBE permission on the source table metadata

For information about granting permissions, see MaxCompute permissions.

Limits

A single MULTI INSERT statement supports up to 255 INSERT operations. Exceeding this limit returns a syntax error.
Each destination partition in a partitioned table can appear only once across all INSERT operations in the statement.
Each non-partitioned table can appear only once across all INSERT operations in the statement.

Syntax

-- Insert from one source scan into multiple destinations
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>
...;

Parameter	Required	Description
`from_statement`	Yes	The `FROM` clause that specifies the data source, such as the source table name.
`table_name`	Yes	The name of the destination table.
`pt_spec`	No	The partition to write data into. Accepts constants only — expressions and functions are not allowed. Format: `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. Each `INSERT` operation must target a different partition.
`select_statement`	Yes	The `SELECT` clause that reads the data to insert.

Usage notes

To enable a full table scan during development, run set odps.sql.allow.fullscan=true; at the start of the session.

Examples

Insert into multiple partitions

This example creates a destination table and inserts sales data from sale_detail into two separate partitions of sale_detail_multi in a single statement.

-- Create a destination table with the same schema as the source table
CREATE TABLE sale_detail_multi LIKE sale_detail;

-- Enable full table scan for this session, then insert into two partitions at once
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;

-- Verify the results
SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail_multi;

Expected output:

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

Duplicate partition — error case

The following statement fails because both INSERT operations target the same partition (sale_date='2010', region='china'). Each partition can appear only once in a MULTI INSERT statement.

-- Error: the same partition cannot be specified more than once in a single MULTI INSERT statement
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;

Example 3: You cannot use both insert overwrite and insert into for different partitions of the same table in a single statement. This operation causes an error. The following is an incorrect command example:

from sale_detail
insert overwrite table sale_detail_multi partition (sale_date='2010', region='china' )
select shop_name, customer_id, total_price
insert into table sale_detail_multi partition (sale_date='2011', region='china' )
select shop_name, customer_id, total_price;