Insert small amounts of data by using the VALUES operation - MaxCompute

Use INSERT ... VALUES or VALUES TABLE to insert a small number of rows directly into a MaxCompute table—typically during development and testing.

For larger datasets (dozens of rows or more), use Tunnel commands to upload a TXT or CSV file instead. See Import data to tables.

Prerequisites

Before you begin, ensure that you have:

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

For details, see MaxCompute permissions.

Supported platforms

Run these statements on any of the following platforms:

Operations

Operation	When to use
`INSERT ... VALUES`	Insert a few or a dozen rows into a test table during the testing phase
`VALUES TABLE`	Perform simple computations on inline data. Can be used in `INSERT` statements and data manipulation language (DML) statements

Limitations

When using INSERT ... VALUES or VALUES TABLE, INSERT OVERWRITE with column specification is not supported. Use INSERT INTO instead.

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>, ...) ...

Parameters

Parameter	Required	Description
`table_name`	Yes	The name of an existing table to insert data into
`pt_spec`	No	The destination partition. Format: `(partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...)`. Required if inserting into a partitioned table
`col_name`	No	The name of the destination column
`col_value`	No	The value to insert. Accepts a literal, a constant expression, or a non-constant expression (such as a built-in function or user-defined function (UDF) expression). Columns not listed in `col_name` default to NULL

Usage notes

Supported value types in VALUES

Literals (string, integer, decimal)
Constant expressions
Non-constant expressions, such as built-in functions and UDF expressions

Unsupported value types in VALUES

The following cannot be used directly in a VALUES list. Use a SELECT expression instead:

Complex data types other than ARRAY. To insert ARRAY data, use SELECT with ARRAY() — see Insert ARRAY data.

Date and time types

To insert DATETIME or TIMESTAMP data, specify the data type prefix in VALUES. For example: datetime'2017-11-11 00:00:00' or TIMESTAMP'2017-11-11 00:00:00.123456789'.

Examples

Insert rows into a specific partition

Create a partitioned table and insert three rows into a named 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 to the srcp table.
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 partition to verify.
SELECT * FROM srcp WHERE p='abc';

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

Insert rows with partition values inline

Specify the partition column value directly in the VALUES list rather than in the PARTITION clause.

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

-- Insert rows and derive the partition value from the VALUES list.
INSERT INTO TABLE srcp PARTITION (p)(key, p) VALUES ('d', '20170101'), ('e', '20170101'), ('f', '20170101');

-- Query the partition to verify.
SELECT * FROM srcp WHERE p='20170101';

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

The value column is not listed in the column list, so it defaults to NULL.

Insert ARRAY data

Because ARRAY literals cannot be used directly in VALUES, use a SELECT expression with ARRAY() instead.

-- 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 to the srcp table.
ALTER TABLE srcp ADD IF NOT EXISTS PARTITION (p='abc');

-- Insert an ARRAY value using a SELECT expression.
INSERT INTO TABLE srcp PARTITION (p='abc') SELECT 'a', ARRAY(1, 2, 3);

-- Query the partition to verify.
SELECT * FROM srcp WHERE p='abc';

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

Insert DATETIME and TIMESTAMP data

Prefix date and time literals with the data type name to avoid type inference errors.

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

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

-- Insert DATETIME and TIMESTAMP values with explicit type prefixes.
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 partition to verify.
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        |
+------------------------+-------------------------+------------+

Use VALUES TABLE with computed columns

Reference the inline values as a named table and apply functions during INSERT.

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

-- Insert data with computed expressions derived from an inline VALUES table t(a, b).
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 partition to verify.
SELECT * FROM srcp WHERE p='20170102';

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

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

Build a constant table without UNION ALL

Use VALUES TABLE as a concise alternative to chained SELECT ... UNION ALL statements.

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

Use VALUES TABLE without a FROM clause

VALUES TABLE lets you run a SELECT statement that calls functions without referencing any table. The underlying implementation selects from an anonymous single-row, zero-column VALUES table, so no DUAL table is needed.

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

-- Insert a row computed entirely from built-in functions.
INSERT INTO TABLE srcp PARTITION (p)
SELECT ABS(-1), LENGTH('abc'), GETDATE();

-- Query all rows.
SET odps.sql.allow.fullscan=true;
SELECT * FROM srcp;

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

Use non-constant expressions in VALUES

Pass function calls directly as column values in a VALUES list.

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

Result:

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