If you want to insert a small amount of data into a table that has limited data space, you can use INSERT … VALUES or VALUES TABLE to perform this operation.

Make sure that you have the ALTER permission on the destination table and the DESCRIBE permission on metadata in the source table before you perform the INSERT INTO operation. For more information, see Permissions.

You can execute the statements that are described in this topic on the following platforms:

Operations

MaxCompute allows you to use the INSERT … VALUES or VALUES TABLE operation to insert a small amount of data into a table.
Operation Description
INSERT... VALUES In the business testing phase, you can use INSERT … VALUES to insert data into a table to perform a simple test.
  • To insert a few or a dozen of data records into the test table, you can use INSERT … VALUES.
  • To insert dozens of data records into the test table, you can use Tunnel commands to upload a TXT or CSV file that contains the data records. For more information, see Import data to tables.
values table To perform simple computing operations on the inserted data, we recommend that you use VALUES TABLE of MaxCompute. VALUES TABLE can be used in INSERT statements and data manipulation language (DML) statements. Features:
  • If no physical tables are available, create a table that contains multiple rows of arbitrary data and perform computing operations on the table.
  • Create a constant table by using VALUES TABLE, instead of the combination of SELECT * FROM and UNION ALL.
  • VALUES TABLE allows you to execute the SELECT statement without the FROM clause. Data of other tables cannot be contained in the expressions of SELECT. The underlying implementation principle is to select data from an anonymous VALUES table that contains only one row and no columns. This way, you can test user-defined functions (UDFs) or other functions without the need to manually create a DUAL table.

Limits

When you use the INSERT ... VALUES or VALUES TABLE operation to insert data into tables, you cannot use INSERT OVERWRITE to specify the columns to which you want to insert data. Instead, you can use only 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>,...)...
  • table_name: required. The name of the table into which you want to insert data. The table must be an existing table.
  • pt_spec: optional. The destination partition into which you want to insert data. The value is in the format of (partition_col1 = partition_col_value1, partition_col2 = partition_col_value2, ...). If you want to update data into a partitioned table, you must specify this parameter.
  • col_name: optional. The name of the destination column into which you want to insert data.
  • col_name: optional. The value of the column in the destination table. If you specify multiple column values, separate them with commas (,). The column values can be constant and non-constant expressions, such as custom function expressions or built-in function expressions. If column values are not specified, the default value is NULL.
    Note
    • Complex data types, except ARRAY, cannot be used to construct constants. For more information about how to use the ARRAY data type in VALUES, see Example 3.
    • If you want to use VALUES to insert data of the DATETIME or TIMESTAMP type, specify the data type in VALUES. For more information, see Example 4.

Examples

  • Example 1: Use INSERT ... VALUES to insert data into a specified partition. Sample statements:
    -- 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 data into the abc partition in the srcp table.
insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);

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

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | 1          | abc        |
| b          | 2          | abc        |
| c          | 3          | abc        |
+------------+------------+------------+
  • Example 2: Use INSERT ... VALUES to insert data into a random partition. Sample statements:
    -- 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.
insert into table srcp partition (p)(key,p) values ('d','20170101'),('e','20170101'),('f','20170101');

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

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d          | NULL       | 20170101   |
| e          | NULL       | 20170101   |
| f          | NULL       | 20170101   |
+------------+------------+------------+
  • Example 3: Use complex data types to construct constants and import data by using INSERT operations. Sample statements:
    -- Create a partitioned table named srcp.
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 data from the srcp table.
select * from srcp where p='abc';

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | [1,2,3]    | abc        |
+------------+------------+------------+
  • Example 4: Perform the INSERT … VALUES operation to insert data of the DATETIME or TIMESTAMP type to the table and specify the data type in values. Sample statements:
    -- 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 in 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 data from the srcp table.
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 insert data into a partitioned table. Sample statements:
    -- 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 data from the srcp table.
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 that a table named t contains a and b columns. The data type of the a column is STRING and that of the b column is BIGINT. Data types of the columns must be derived from the VALUES list.
  • Example 6: Construct a constant table by using VALUES TABLE, instead of the combination of SELECT * FROM and UNION ALL. Sample statements:
    select 1 c union all select 2 c;
-- The preceding statement is equivalent to the following statement:
select * from values (1), (2) t(c);

-- Return result
+------------+
| c          |
+------------+
| 1          |
| 2          |
+------------+
  • Example 7: Use VALUES TABLE without the FROM clause to insert data. Sample statements:
    -- 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 data from the srcp table.
select * from srcp;

-- Return result
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| 1          | 3          | 2020-11-25 18:39:48 |
+------------+------------+------------+
  • Example 8: Use non-constant expressions. Sample statements:
    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          |
+------------+