INSERT INTO SELECT FROM，从某个表复制到另外一个表语句的使用方法

INSERT INTO SELECT FROM，从某个表复制到另外一个表语句的使用方法，数据脚本迁移原本看起来比较麻烦，问了下大佬，大佬随便在我屏幕上打上：insert into from，说用这个就好了，然后再加上小度就找到解决方法了，感谢大佬，每天教会小弟一点知识。

select into from 和 insert into select都是用来复制表

两者的主要区别为： select into from 要求目标表不存在，因为在插入时会自动创建，insert into select from 要求目标表存在

备份表数据： create table emp as select * from scott.emp

还原表数据：insert into emp select * from scott.emp

复制表结构及其数据：create table table_name_new as select * from table_name_old

只复制表结构：

create table table_name_new as select * from table_name_old where 1=2

或者：

create table table_name_new like table_name_old

只复制表数据：

如果两个表结构一样：

insert into table_name_new select * from table_name_old

如果两个表结构不一样：

insert into table_name_new(column1,column2...) select column1,column2... from table_name_old pasting

实例：

INSERT INTO `user1` (

      openid,

      brand_id,

      brand_pid,

      start_date,

      end_date

) 

SELECT 

      openid,

      id AS brand_id,

      pid AS brand_pid,

      group_start_date AS start_date,

      group_end_date AS end_date 

FROM

      `user` 

WHERE oepnids IN (

        'b2c8edd0f2a2496b9b6513a3dc10bd68',

        '28407233fe054bf09cfed6bc3b491a40',

        '3edc4bb7ab3247b98ff4e7760083916c',

        '2b09b3d6cf8f43c7a0aab4226de9d6f2',

        'a15f8b57907741cda11888b14212d329',

        '1ddcf80194ec4abd8fc75b937379516f',

  )