MULTI INSERT-MaxCompute(MaxCompute)-阿里云帮助中心

Use MULTI INSERT to insert data from a single source query into multiple destination tables or partitions within one SQL statement. Each row from the source table is read only once, and the results of different SELECT clauses are written to their respective destination tables or partitions using INSERT INTO or INSERT OVERWRITE operations.

Prerequisites

Before you run a MULTI INSERT statement, make sure you have the following permissions:

Permission	Scope
Alter	Each destination table
Describe	The metadata of the source table

For information about how to grant these permissions, see MaxCompute permissions.

Limits

A single MULTI INSERT statement supports a maximum of 255 INSERT operations. If the number of INSERT operations exceeds 255, a syntax error is returned.

The following table describes the limits that apply to partitioned and non-partitioned tables.

Table type	Limit
Partitioned table	You cannot specify the same destination partition for multiple `INSERT` operations within the same statement.
Non-partitioned table	You cannot perform `INSERT OVERWRITE` more than once in the same statement.
Non-partitioned table	You cannot mix `INSERT OVERWRITE` and `INSERT INTO` operations in the same statement.
Non-partitioned table	You can perform `INSERT INTO` multiple times in the same statement.

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

Parameters

Parameter	Required	Description
`from_statement`	Yes	The `FROM` clause that specifies the data source. For example, you can specify the name of a source table in this clause.
`table_name`	Yes	The name of the destination table into which you want to insert data.
`pt_spec`	No	The partition into which you want to insert data. Only constants are allowed. Expressions such as functions are not allowed. Specify values in the format `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. When you insert data into multiple partitions (for example, `pt_spec1` and `pt_spec2`), the partition specifications must be different from each other.
`select_statement`	Yes	The `SELECT` clause that queries data from the source table. Each `INSERT` clause can have its own `SELECT` clause to determine which data is written to the corresponding destination table or partition.

Examples

Insert data into multiple partitions of the same table

The following example reads data from the sale_detail table and inserts it into two partitions of the sale_detail_multi table: one for 2010 sales and one for 2011 sales in the China region.

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

Query the data in the sale_detail_multi table to verify the results:

-- Enable a full table scan only for the current session.
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      |
+------------+-------------+-------------+------------+------------+

Error: Duplicate partition specification

If you specify the same destination partition for multiple INSERT operations in a single MULTI INSERT statement, an error is returned. The following statement is incorrect:

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;

This statement fails because both INSERT OVERWRITE operations target the same partition (sale_date='2010', region='china'). To fix this, make sure each INSERT operation specifies a different partition.