将一张表的数据插入另外一张表
表cmb_send_sms
CREATE TABLE `cmb_send_sms` ( `send_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id', `phone_no` varchar(32) NOT NULL COMMENT '手机号码', `status` varchar(2) NOT NULL COMMENT '预约短信下发状态,1待发送,2发送成功,3校验失败,4发送失败', `sms_id` bigint(20) DEFAULT NULL COMMENT '预约短信id', `join_flag` varchar(2) NOT NULL COMMENT '是否参加标记,1参加成功,0未参加,2参加失败', `succ_status` varchar(2) NOT NULL COMMENT '预约成功短信下发状态,1待发送,2已完成,3发送失败', `succ_sms_id` bigint(20) DEFAULT NULL COMMENT '预约成功短信id', `start_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '活动开始时间', `end_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '活动结束时间', `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `create_user` varchar(32) DEFAULT NULL COMMENT '创建人', PRIMARY KEY (`send_id`), UNIQUE KEY `cmb_send_sms_phone` (`phone_no`) ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='离线短信发送配置表';
表activity_white_list
CREATE TABLE `activity_white_list` (
`white_list_id` bigint(
20
) NOT NULL AUTO_INCREMENT COMMENT
'主键ID'
,
`activity_white_list_file_id` bigint(
20
) NOT NULL COMMENT
'活动白名单文件ID'
,
`phone_number` varchar(
11
) NOT NULL COMMENT
'手机号'
,
`marketing_activity_id` bigint(
20
) DEFAULT NULL COMMENT
'营销活动ID'
,
PRIMARY KEY (`white_list_id`)
) ENGINE=InnoDB AUTO_INCREMENT=
613962
DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
将activity_white_list中的数据导入cmb_send_sms
insert into cmb_send_sms (phone_no,status,sms_id,join_flag,succ_status,succ_sms_id,start_time,end_time,create_time,create_user) select phone_number as phone_no,'1' as status,null as sms_id, '0' as join_flag,'1' as succ_status,null as succ_sms_id, null as start_time,null as end_time,NOW() as create_time,'system' as create_user from activity_white_list where marketing_activity_id=100184