mysql 复制A表 到B表;insert into select * from table

时间:2022-11-03 16:24:09

情况一:

INSERT INTO tb1 (a,b,c) select a1,b1,c1, from tb2 where ....

--  案例 百度云  

 

INSERT INTO l_biz_car_online_entry
(online_id, car_id, product_line_id, model_name, model_id, product_line_name, product_resource_no, device_no, serial_no
, device_count, device_state, check_state, check_date, check_id, check_name, del_flag, create_id, create_name
, create_time, update_id, update_name, update_time, version, data_source_type)
select
FORMAT((@rowNum:=@rowNum+1),0) as rowNo
,a.car_id
,a.product_line_id
,c.model_name
,a.model_id
,d.product_line_name
,a.product_resource_no
,a.device_no
,a.serial_no
,(select count(1) from l_biz_device a1 where a1.del_flag=0 and a1.car_id=a.car_id)device_count
,2 device_state
,0 check_state
,null check_date
,null check_id
,null check_name
,0 del_flag
,1 create_id
,'admin' create_name
,NOW() create_time
,1 update_id
,'admin' update_name
,NOW() update_time
,1 version
,2 data_source_type
FROM l_biz_car_info a
left join l_biz_device b
on a.device_no=b.device_no
left join l_biz_model c on a.model_id=c.id
left join l_sys_product d on a.product_line_id=d.product_line_id
where a.sale_state=1 and b.device_status=2 and ifnull(a.del_flag,0)=0
;

 

情况二:  表数据备份

create table l_biz_car_info_copy (select * from l_biz_car_info);