Use the VALUES clause to insert small amounts of data into a table-MaxCompute(MaxCompute)-阿里云帮助中心

Use the INSERT … VALUES statement or the VALUES TABLE clause to insert small amounts of data into a table.

Running an INSERT INTO statement requires the ALTER permission for the destination table and the DESCRIBE permission for the source table. For information about how to grant permissions, see MaxCompute permissions.

You can run the commands in this topic on the following platforms:

Features

MaxCompute lets you use INSERT … VALUES or VALUES TABLE to insert small amounts of data into a table.

Operation

Description

INSERT … VALUES

During testing, you can use INSERT … VALUES to quickly add test data to a table.

Use an INSERT … VALUES statement to insert a small amount of data.
To insert a larger number of rows, use Tunnel to upload a TXT or CSV file. For more information, see Import data. You can also use the import feature of DataWorks to quickly Import a data file. For more information, see DataStudio feature guide.

VALUES TABLE

For simple calculations on inserted data, use the VALUES TABLE clause. The VALUES TABLE clause can be used in INSERT statements and any DML statements. It provides the following features:

Simulate a multi-row table with arbitrary data without using a physical table, which enables you to perform calculations on the fly.
Construct a constant table more efficiently than using a combination of SELECT * FROM and UNION ALL.
Use a special form of VALUES TABLE to run a SELECT statement without a FROM clause. The expressions in the SELECT list cannot reference data from other tables. This is implemented by selecting from an anonymous, single-row, zero-column VALUES table. This method is useful for testing a user-defined function (UDF) or other functions without creating a DUAL table.

Limitations

When you use INSERT … VALUES or VALUES TABLE to insert data into a table, you cannot use INSERT OVERWRITE to specify the destination columns. You must use INSERT INTO.

Syntax

--INSERT … VALUES
INSERT INTO TABLE <table_name>
[PARTITION (<pt_spec>)][(<col1_name> ,<col2_name>,...)] 
VALUES (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),...

--VALUES table
VALUES (<col1_value>,<col2_value>,...),(<col1_value>,<col2_value>,...),<table_name> (<col1_name> ,<col2_name>,...)...

Parameter	Required	Description
table_name	Yes	The name of the table to insert data into. The table must already exist.
pt_spec	No	The destination partition. The format is `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. This parameter is required if you are inserting data into a partitioned table.
col_name	No	The name of a destination column.
col_value	No	The value for the corresponding column. Separate multiple values with commas (,). This can be a constant or a non-constant expression, such as a user-defined function or a built-in function. If a value is not specified for a column, the column defaults to NULL.

Note

The VALUES clause does not support literals for complex data types, such as ARRAY. Inserting ARRAY data requires a different syntax. For details, see Example 3.
When you use VALUES to write DATETIME or TIMESTAMP data, you need to specify the type name in VALUES. See Example 4.

Examples

Example 1: Use INSERT … VALUES to insert data into a specific partition.

-- Create a partitioned table named srcp.
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

-- Add a partition to the srcp table.
ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');

-- Insert data into the 'abc' partition of the srcp table.
INSERT INTO TABLE srcp PARTITION (p='abc') VALUES ('a',1),('b',2),('c',3);

-- Query the srcp table.
SELECT * FROM srcp WHERE p='abc';

-- The following result is returned.
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | 1          | abc        |
| b          | 2          | abc        |
| c          | 3          | abc        |
+------------+------------+------------+

Example 2: Use INSERT … VALUES to insert data without specifying a partition value.

-- Create a partitioned table named srcp.
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

-- Insert data into the srcp table without specifying a partition value. The partition column is included in the column list.
INSERT INTO TABLE srcp PARTITION (p)(key,p) VALUES ('d','20170101'),('e','20170101'),('f','20170101');

-- Query the srcp table.
SELECT * FROM srcp WHERE p='20170101';

-- The following result is returned.
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d          | NULL       | 20170101   |
| e          | NULL       | 20170101   |
| f          | NULL       | 20170101   |
+------------+------------+------------+

Example 3: Use an INSERT statement with a SELECT clause to insert data of a complex data type.

-- Create a partitioned table named srcp with a complex data type.
CREATE TABLE IF NOT EXISTS srcp (key STRING,value ARRAY<INT>) PARTITIONED BY (p STRING);

-- Add a partition to the srcp table.
ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');

-- Insert data into the 'abc' partition of the srcp table.
INSERT INTO TABLE srcp PARTITION (p='abc') SELECT 'a', ARRAY(1, 2, 3);

-- Query the srcp table.
SELECT * FROM srcp WHERE p='abc';

-- The following result is returned.
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | [1,2,3]    | abc        |
+------------+------------+------------+

Example 4: Use INSERT … VALUES to write DATETIME or TIMESTAMP data. You must explicitly cast the literal by prefixing it with the type name in the VALUES clause.

-- Create a partitioned table named srcp.
CREATE TABLE IF NOT EXISTS srcp (key STRING, value TIMESTAMP) PARTITIONED BY (p STRING);

-- Add a partition to the srcp table.
ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');

-- Insert data into the 'abc' partition of the srcp table.
INSERT INTO TABLE srcp PARTITION (p='abc') VALUES (datetime'2017-11-11 00:00:00', TIMESTAMP'2017-11-11 00:00:00.123456789');

-- Query the srcp table.
SELECT * FROM srcp WHERE p='abc';

-- The following result is returned.
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 2017-11-11 00:00:00 | 2017-11-11 00:00:00.123 | abc        |
+------------+------------+------------+

Example 5: Use a VALUES table to insert data.

-- Create a partitioned table named srcp.
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

-- Insert data into the srcp table.
INSERT INTO TABLE srcp PARTITION (p) SELECT concat(a,b), length(a)+length(b),'20170102' FROM VALUES ('d',4),('e',5),('f',6) t(a,b);

-- Query the srcp table.
SELECT * FROM srcp WHERE p='20170102';

-- The following result is returned.
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d4         | 2          | 20170102   |
| e5         | 2          | 20170102   |
| f6         | 2          | 20170102   |
+------------+------------+------------+

The database infers the column data types (in this case, STRING and BIGINT) from the values in the VALUES list. VALUES (…), (…) t(a, b) defines a temporary table named t with columns a and b.

Example 6: Use a VALUES table to construct a constant table as an alternative to using SELECT * FROM with UNION ALL.

SELECT 1 c UNION ALL SELECT 2 c;
-- The preceding statement is equivalent to the following statement:
SELECT * FROM VALUES (1), (2) t(c);

-- The following result is returned.
+------------+
| c          |
+------------+
| 1          |
| 2          |
+------------+

Example 7: Use a special form of a VALUES table to insert data without a FROM clause.

-- Create a partitioned table named srcp.
CREATE TABLE IF NOT EXISTS srcp (key STRING,value BIGINT) PARTITIONED BY (p STRING);

-- Insert data into the srcp table.
INSERT INTO TABLE srcp PARTITION (p) SELECT ABS(-1), LENGTH('abc'), GETDATE();

-- Query the srcp table.
SET odps.sql.allow.fullscan=true;
SELECT * FROM srcp;

-- Sample result:
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 1          | 3          | 2024-12-10 16:58:56 |
+------------+------------+------------+

Example 8: Use non-constant expressions.

SELECT * FROM VALUES (GETDATE()),(TO_DATE('20190101', 'yyyyMMdd')),(LASTDAY(DATETIME '2019-01-01 01:10:00')) t(d);

The following result is returned:

+------------+
| d          |
+------------+
| 2019-01-31 00:00:00 |
| 2019-01-01 00:00:00 |
| 2024-12-10 16:52:36 |
+------------+