本文为您介绍INSERT…VALUES语句,常用于为小表插入少量数据的场景。

背景信息

通常在业务测试阶段,需要在表中插入一些数据进行简单的测试:
  • 如果是为一个小表插入几条或十几条数据,您可以使用INSERT … VALUES方法在测试表中快速写入数据。
  • 如果数据量较大,您可以通过Tunnel上传一个txtcsv数据文件写入数据,详情请参见导入数据

    或者通过DataWorks的导入功能快速导入一个数据文件,详情请参见界面功能点介绍

注意 INSERT OVERWRITE不支持指定插入列的功能,只能使用INSERT INTO实现。

命令说明

命令格式
INSERT INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2,...)][(co1name1,colname2,...)] 
[VALUES (col1_value,col2_value,...),(col1_value,col2_value,...),...]
参数说明
  • tablename:要插入数据的目标表名称。此名称需为已经存在的表名称。
  • PARTITION (partcol1=val1, partcol2=val2,...)]:分区信息。如果需要更新的表为分区表,需要指定该参数。
  • [(co1name1,colname2,...):目标表中的字段名称。
  • col_value:目标表中列对应的列值,多个列值之间用逗号(,)分隔。此列值必须为常量,列值未指定时,缺省为NULL。
    说明
    • VALUES只支持常量不支持函数,但是ARRAY等复杂类型无法构造对应的常量,此时您可以通过如下语句实现在VALUES中使用ARRAY类型的需求。
      insert into table srcp (p='abc') select 'a', array('1', '2', '3');
    • 通过VALUES写入DATETIME、TIMESTAMP类型数据时,需要在VALUES中指定类型名称,如下所示。
      insert into table srcp (p='abc') values (datetime'2017-11-11 00:00:00',timestamp'2017-11-11 00:00:00.123456789');

示例

  • 特定分区内插入数据。
    --删除已存在的表srcp。
    drop table if exists srcp;
    --创建分区表srcp。
    create table if not exists srcp (key string,value bigint) partitioned by (p string);
    --给表srcp的指定分区abc中插入数据。
    insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);
    --查询表srcp。
    select * from srcp where p='abc';
    +-----+------------+---+
    | key | value      | p |
    +-----+------------+---+
    | a   | 1          | abc |
    | b   | 2          | abc |
    | c   | 3          | abc |
    +-----+------------+---+
  • 非特定分区内插入数据
    --删除已存在的表srcp。
    drop table if exists srcp;
    --创建分区表srcp。
    create table if not exists srcp (key string,value bigint) partitioned by (p string);
    --给表srcp的指定分区abc中插入数据。
    insert into table srcp partition (p)(key,p) values ('d','20170101'),('e','20170101'),('f','20170101');
    --查询表srcp。
    select * from srcp where p='20170101';
    +-----+------------+---+
    | key | value      | p |
    +-----+------------+---+
    | d   | NULL       | 20170101 |
    | e   | NULL       | 20170101 |
    | f   | NULL       | 20170101 |
    +-----+------------+---+

VALUES TABLE功能

使用INSERT … VALUES时,VALUES后的取值必须是常量。如果需要在插入的数据中进行一些简单的运算,建议使用MaxCompute的VALUES TABLE功能。

VALUES TABLE并不仅限于在INSERT语句中使用,任何DML语句都可以使用。VALUES TABLE功能的使用方式如下:
  • 在没有任何物理表时,您可以模拟一个有任意数据的、多行的表,并进行任意运算。
    下例中的values (…), (…) t(a, b)相当于定义了一个名为t,列为ab,类型分别为STRING、BIGINT的表。列的类型需从VALUES列表中推导。
    --删除已存在的表srcp。
    drop table if exists srcp;
    --创建分区表srcp。
    create table if not exists srcp (key string,value bigint) partitioned by (p string);
    --为表srcp中插入数据。
    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);
    --查询表srcp。
    select * from srcp where p='20170102';
    +-----+------------+---+
    | key | value      | p |
    +-----+------------+---+
    | d4  | 2          | 20170102 |
    | e5  | 2          | 20170102 |
    | f6  | 2          | 20170102 |
    +-----+------------+---
  • 取代select * from dualunion all组合的方式,拼出常量表,如下所示。
    select 1 c from dual 
    union all
    select 2 c from dual;
    --等同于以下语句。
    select * from values (1), (2) as t(c);
  • VALUES TABLE的特殊形式。您可以不写FROM语句,直接执行SELECT(只要SELECT表达式列表中不出现上游表的数据),如下例所示。其底层实现为从一个1行0列的匿名VALUES表中进行选取操作。这样,在您测试UDF或其它函数时,可免去手工创建DUAL表的过程。
    select abs(-1), length('abc'), getdate();