语法格式:
INSERT INTO [database_name.]table_name [partition_clause] [col_name_list] VALUES
(...),(...),...
或
INSERT
INTO
[database_name.]table_name
[partition_clause]
[col_name_list]
SELECT ... FROM [database_name.]table_name ...
partition_clause:
PARTITION (partcol1[=val1] [, partcol2[=val2] ...])
col_name_list:
(col1[,col2 ...])
INSERT 将新行插入到一个已存在的表中。INSERT...VALUES 形式的语句基于明确
的值插入记录行。INSERT ... SELECT 形式的语句从另一个或多个表中选取出值,
并将其插入。
参数说明如下:
table_name:是要被插入数据的表。
col_name_list:指出语句指定的值赋给哪些列。
如果在INSERT...VALUES 或INSERT...SELECT 中没有指定col_name_list,那么所
有列的值必须在VALUES()列表中或由SELECT 提供。
partition_clause: 如果目标表是分区表,则必须使用partition_clause 语法。分区表的
插入分为静态分区INSERT 和动态分区INSERT,见后文详细介绍。
示例1:INSERT INTO...
gbase> CREATE TABLE t0(id int) ENGINE = 'HIVE';
Query OK, 0 rows affected
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 755
gbase> INSERT INTO t0 VALUES(1),(2),(3),(4),(5),(6),(2),(3),(1);
Query OK, 9 rows affected
Records: 9 Duplicates: 0 Warnings: 0
gbase> SELECT * FROM t0;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 2 |
| 3 |
| 1 |
+------+
9 rows in set
示例2:INSERT INTO...SELECT...
gbase> CREATE TABLE t0(id int) ENGINE = 'HIVE';
Query OK, 0 rows affected
gbase> CREATE TABLE ssbm(lineorder int,name varchar(50)) ENGINE = 'HIVE';
Query OK, 0 rows affected
gbase> INSERT INTO ssbm VALUES(1,'baker'),(2,'hunter'),(3,'carter'),(4,'smith');
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
gbase> INSERT INTO t0(id) SELECT s.lineorder FROM ssbm s;
Query OK
gbase> SELECT * FROM t0;
+-------+
| id |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 756
+-------+
4 rows in set
示例3:INSERT INTO ... PARTITION(...)
gbase> CREATE TABLE t0(id varchar(64),linked varchar(100)) PARTITIONED BY (dt
varchar(20)) CLUSTERED BY(id) INTO 256 BUCKETS STORED AS ORC ENGINE = 'HIVE';
Query OK, 4 rows affected
gbase> INSERT INTO t0 PARTITION(dt = '2016-03-14') VALUES('gbase','mail.com'),
('hadoop','apache.org') ;
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
gbase> INSERT INTO t0 PARTITION(dt) VALUES('gbase','mail.com','2016-03-15'),
('hadoop','apache.org','2016-03-16') ;
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
gbase> SELECT * FROM t0;
+--------+------------+------------+
| id | addr | dt |
+--------+------------+------------+
| gbase | mail.com | 2016-03-14 |
| hadoop | apache.org | 2016-03-14 |
| gbase | mail.com | 2016-03-15 |
| hadoop | apache.org | 2016-03-16 |
+--------+------------+------------+
4 rows in set