This topic describes how to use the INSERT...VALUES statement to insert data into a table that contains small volumes of data.

Background information

In the business testing phase, you must insert data into a table for simple testing:
  • To insert several or a dozen data records, you can use the INSERT…VALUES statement to quickly write the data records to a test table.
  • To insert dozens of data records, you can use Tunnel to upload a TXT or CSV file that contains the data records. For more information, see Import data.
Notice You can use the INSERT INTO statement instead of the INSERT OVERWRITE statement to insert data into specified columns.

Syntax

The following code describes the syntax of the INSERT…VALUES statement:
INSERT INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2,...)][(co1name1,colname2,...)] 
[VALUES (col1_value,col2_value,...),(col1_value,col2_value,...),...]
Parameters:
  • tablename: the name of the table into which you want to insert data. The table must be an existing table.
  • PARTITION (partcol1=val1, partcol2=val2,...)]: the information about partitions. If you want to insert data into a partitioned table, you must specify this parameter.
  • [(co1name1,colname2,...): the names of the columns in the target table.
  • col_value: the value of the specified column in the target table. Separate multiple values with commas (,). Each value in the column must be a constant. If no values are specified, NULL is used.
    Note
    • VALUES supports only constants, not functions. Constants cannot be constructed for some complex data types, such as ARRAY. However, you can use the following statement to pass values of the ARRAY type to VALUES:
      INSERT INTO TABLE srcp (p='abc') SELECT 'a', ARRAY('1', '2', '3');
    • To pass values of the DATETIME or TIMESTAMP type, you must specify the data type in VALUES. Example:
      INSERT INTO TABLE srcp (p='abc') VALUES (datetime'2017-11-11 00:00:00',timestamp'2017-11-11 00:00:00.123456789');

Examples

  • Insert data into a specific partition
    -- Delete the srcp table that already exists in the system.
    DROP TABLE IF EXISTS srcp;
    -- Create a partitioned table named srcp.
    CREATE TABLE IF NOT EXISTS srcp (key string,value bigint) PARTITIONED BY (p string);
    -- 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 data from the srcp table.
    SELECT * FROM srcp WHERE p='abc';
    +-----+------------+---+
    | key | value      | p |
    +-----+------------+---+
    | a   | 1          | abc |
    | b   | 2          | abc |
    | c   | 3          | abc |
    +-----+------------+---+
  • Insert data into any partition
    -- Delete the srcp table that already exists in the system.
    DROP TABLE IF EXISTS srcp;
    -- Create a partitioned table named srcp.
    CREATE TABLE IF NOT EXISTS srcp (key string,value bigint) PARTITIONED BY (p string);
    -- Insert data into a partition of the srcp table.
    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';
    +-----+------------+---+
    | key | value      | p |
    +-----+------------+---+
    | d   | NULL       | 20170101 |
    | e   | NULL       | 20170101 |
    | f   | NULL       | 20170101 |
    +-----+------------+---+

Features of VALUES TABLE

When you use INSERT… VALUES, the values after VALUES must be constants. If you want to perform simple computing operations on the inserted data, we recommended that you use VALUES TABLE of MaxCompute.

VALUES TABLE can be used in INSERT and DML statements. You can use VALUES TABLE in the following scenarios:
  • If no tables are available, create a table that contains multiple rows of data and perform computing operations on the table.
    For example, VALUES (…), (…) t(a, b) defines that a table named t contains the a and b columns. The data type of the a column is STRING and that of the b column is BIGINT. The data type of a column must be derived from the VALUES list.
    -- Delete the srcp table that already exists in the system.
    DROP TABLE IF EXISTS srcp;
    -- 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';
    +-----+------------+---+
    | key | value      | p |
    +-----+------------+---+
    | d4  | 2          | 20170102 |
    | e5  | 2          | 20170102 |
    | f6  | 2          | 20170102 |
    +-----+------------+---
  • Use VALUES TABLE rather than the combination of SELECT * FROM DUAL and UNION ALL to create a constant-type table.
    SELECT 1 c FROM DUAL 
    UNION ALL
    SELECT 2 c FROM DUAL;
    -- Equivalent to the following statement:
    SELECT * FROM VALUES (1), (2) AS t(c);
  • Special forms of VALUES TABLE. If the expressions of the SELECT statement do not contain upstream table data, you can execute the SELECT statement without the FROM clause. In this case, the underlying implementation 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 DUAL tables.
    select abs(-1), length('abc'), getdate();