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原表内容一致。

注意：

1. 若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';