文章目录
- 一、insert into select
- 二、insert into select插入失败
本篇文章主要讲解insert into select 的用法，以及insert into select的坑或者注意事项。本篇文章中的sql基于mysql8.0进行讲解
一、insert into select
该语法常用于从另一张表查询数据插入到某表中。如备份表的时候，将表中的数据迁移到备份表中。
以下测试insert into select用于备份表
create table if not exists user
(
id int not null primary key,
sex char(3) null,
NAME char(20) null
);
INSERT INTO user VALUES
(1,'nan','陈一'),
(2,'nv','珠二'),
(3,'nv','张三'),
(4,'nan','李四'),
(5,'nv','王五'),
(6,'nan','赵六');
备份表sql
-- 创建bak备份表
create table user_bak_20230731 like user;
-- 将user表的数据插入到备份表
insert into user_bak_20230731 select * from user;
备份表数据如下，可以看到与user原表内容一致。
注意：
- 若insert into select一次性插入数据量过多，建议分批插入，避免io异常或缓存不足。
二、insert into select插入失败
失败场景： hive数据库中，针对分区表直接执行insert into select时报错。（其他类型数据库针对分区表直接执行insert into select没发现这个问题，mysql数据库亲测是ok的）
错误日志：
FAILED: SemanticException 1:23 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'user_tmp'
解决方法
需要在插入的数据中指定分区字段的数值是多少。
比如：
建表语句
create table test1 (
starttime string,
endtime string,
title string
)
PARTITIONED BY (username string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '| '
STORED AS TEXTFILE;
-- 创建另一张表
create table test2 like test1;
插入语句：
insert into table test2 PARTITION(username='admin') select starttime, endtime, title from test1 where username = 'admin';
