insert into select用法

傷城~ 2023-10-14 10:51 65阅读 0赞

#### 文章目录 ####

*  一、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','赵六');

![在这里插入图片描述][06f6ca94b02f44b7b07d2e0b8cec0055.png]  
备份表sql

-- 创建bak备份表
    create table user_bak_20230731 like user;
    -- 将user表的数据插入到备份表
    insert into user_bak_20230731 select * from user;

备份表数据如下，可以看到与user原表内容一致。  
![在这里插入图片描述][7dee55526b4241dd9049743b735c8ea2.png]

`注意：`

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';

[06f6ca94b02f44b7b07d2e0b8cec0055.png]: https://img-blog.csdnimg.cn/06f6ca94b02f44b7b07d2e0b8cec0055.png
[7dee55526b4241dd9049743b735c8ea2.png]: https://img-blog.csdnimg.cn/7dee55526b4241dd9049743b735c8ea2.png