Use VALUES to insert data into tables - MaxCompute

If you need to insert a small number of rows during development or testing, use INSERT ... VALUES or VALUES TABLE.

Before you run INSERT INTO, make sure that you have:

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

For more information, see Permissions.

Run the statements in this topic on any of the following platforms:

Choose an operation

Operation	When to use
`INSERT ... VALUES`	Inserting a few to a dozen rows during business testing. For dozens of rows, use Tunnel commands to upload a TXT or CSV file instead. For more information, see Import data to tables.
`VALUES TABLE`	Running simple computing operations on inserted data. Works in `INSERT` statements and other DML statements.

VALUES TABLE supports three additional patterns not available with INSERT ... VALUES:

Create a multi-row in-memory table without a physical table, and run computing operations on it.
Build a constant table as a shorter alternative to SELECT * FROM ... UNION ALL.
Run a SELECT statement without a FROM clause to test user-defined functions (UDFs) or built-in functions, without creating a DUAL table. The underlying implementation selects from an anonymous VALUES table with one row and no columns. Expressions in the SELECT list cannot reference other tables.

Limitations

Insert mode: Only INSERT INTO is supported. INSERT OVERWRITE cannot be used to specify columns with VALUES.
Complex data types: Only ARRAY can be used to construct constants directly in a VALUES list. Other complex types are not supported. To insert ARRAY values, use INSERT ... SELECT with the array() function instead. See Example 3.
DATETIME and TIMESTAMP values: Prefix each literal with its data type name in the VALUES list. For example, use datetime'2017-11-11 00:00:00' instead of '2017-11-11 00:00:00'. See Example 4.

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 target table. The table must already exist.
pt_spec	No	The destination partition, in the format `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. Required when inserting into a partitioned table.
col_name	No	The name of the destination column.
col_value	No	The value to insert into the corresponding column. Separate multiple values with commas (`,`). Valid values: a constant, a built-in function expression, or a custom function expression. Defaults to `NULL` if not specified.

Examples

Example 1: Insert rows 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 the abc partition.
alter table srcp add if not exists partition (p='abc');

-- Insert three rows into the abc partition.
insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);

-- Query the inserted data.
select * from srcp where p='abc';

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | 1          | abc        |
| b          | 2          | abc        |
| c          | 3          | abc        |
+------------+------------+------------+

Example 2: Insert rows and let MaxCompute assign the partition value

-- Create a partitioned table named srcp.
create table if not exists srcp (key string,value bigint) partitioned by (p string);

-- Insert rows, specifying only the key and p columns. The value column defaults to NULL.
insert into table srcp partition (p)(key,p) values ('d','20170101'),('e','20170101'),('f','20170101');

-- Query the inserted data.
select * from srcp where p='20170101';

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d          | NULL       | 20170101   |
| e          | NULL       | 20170101   |
| f          | NULL       | 20170101   |
+------------+------------+------------+

Example 3: Insert ARRAY values

Only ARRAY supports constant construction in a VALUES list. For other complex types, use INSERT ... SELECT with the corresponding constructor function. The following example uses the array() function.

-- Create a partitioned table named srcp.
create table if not exists srcp (key string,value array<int>) partitioned by (p string);

-- Add the abc partition.
alter table srcp add if not exists partition (p='abc');

-- Insert a row with an ARRAY value using SELECT.
insert into table srcp partition (p='abc') select 'a', array(1, 2, 3);

-- Query the inserted data.
select * from srcp where p='abc';

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | [1,2,3]    | abc        |
+------------+------------+------------+

Example 4: Insert DATETIME and TIMESTAMP values

Prefix each temporal literal with its data type name in the VALUES list. Without the prefix, MaxCompute cannot parse the value correctly.

-- Create a partitioned table named srcp.
create table if not exists srcp (key string, value timestamp) partitioned by (p string);

-- Add the abc partition.
alter table srcp add if not exists partition (p='abc');

-- Insert a row with typed datetime and timestamp literals.
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 inserted data.
select * from srcp where p='abc';

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 2017-11-11 00:00:00 | 2017-11-11 00:00:00.123 | abc        |
+------------+------------+------------+

Example 5: Use VALUES TABLE to compute and insert data

VALUES (...), (...) t(a, b) defines an inline table named t with columns a and b. Column types are inferred from the VALUES list: a is STRING and b is BIGINT.

-- Create a partitioned table named srcp.
create table if not exists srcp (key string,value bigint) partitioned by (p string);

-- Use VALUES TABLE to apply functions and insert computed results.
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 inserted data.
select * from srcp where p='20170102';

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d4         | 2          | 20170102   |
| e5         | 2          | 20170102   |
| f6         | 2          | 20170102   |
+------------+------------+------------+

Example 6: Build a constant table with VALUES TABLE

VALUES TABLE is a shorter alternative to SELECT ... UNION ALL when constructing a constant table.

-- These two statements are equivalent.
select 1 c union all select 2 c;
select * from values (1), (2) t(c);

-- Return result
+------------+
| c          |
+------------+
| 1          |
| 2          |
+------------+

Example 7: Run SELECT without a FROM clause

When no source table is needed, omit the FROM clause. MaxCompute selects from an anonymous single-row table internally, so you can test UDFs and built-in functions without creating a DUAL table.

-- Create a partitioned table named srcp.
create table if not exists srcp (key string,value bigint) partitioned by (p string);

-- Insert results of built-in functions without a FROM clause.
insert into table srcp partition (p) select abs(-1), length('abc'), getdate();

-- Query the inserted data.
select * from srcp;

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 1          | 3          | 2020-11-25 18:39:48 |
+------------+------------+------------+

Example 8: Use non-constant expressions

select * from values ('a'),(to_date('20190101', 'yyyyMMdd')),(getdate()) t(d);

The following result is returned:

+------------+
| d          |
+------------+
| 2021-02-01 18:01:38 |
| 2019-01-01 00:00:00 |
| a          |
+------------+