mysql 插多行数据

时间:2022-09-20 20:06:28

应用场景:

  需要把一个表(tableA)的个别字段筛选出来,添加到新表中(tableB)。新表还含有其他字段,主键是uuid。

思路解析:

  熟悉插入一行数据的sql语句:

insert into customer_healthinfo(PersonalInfoID, CustomerID, ProjectType)
values(REPLACE(UUID(),"-",""),'110104198002204481','20140201')

  如果数据量极少:

insert into customer_healthinfo(PersonalInfoID, CustomerID, ProjectType)
values(REPLACE(UUID(),"-",""),'110104198002204481','20140201'),
values(REPLACE(UUID(),"-",""),'110104198002204481','20140201'),
values(REPLACE(UUID(),"-",""),'110104198002204481','20140201')

  数据量大的时候,尝试添加一个中间表,类似于:

insert into customer_healthinfo(PersonalInfoID, CustomerID, ProjectType)
select PersonalInfoID, CustomerID, ProjectType from temp_C

 sql语句具体实施:

  1.先创建一个新表(table_C,也可以是临时表),存放table_A筛选出来的数据以及一个空字段ID用于存放UUID。

  2.table_C修改ID字段内容为UUID:

update table_C set id = REPLACE(UUID(),"-","")

   3.等同于一个表导入到另一个表的简单问题:

insert into customer_healthinfo(PersonalInfoID, CustomerID, ProjectType)
select PersonalInfoID, CustomerID, ProjectType from temp_C