从临时表插入会员至member_info_svc表
insert into member_info_svc (
gh_no,chname,sex,birthday,tel,email,title,card_no,membership_type,enroll_date,expiry_date,id_no,
nationality,sub_type,vip_level,member_source,duty_code,language_code,member_department_code,id_type,
linkman_name,address,engname,card_password,insert_user,insert_date)
select card_no,chname,sex,birthday,tel,email,title,card_no,membership_type,enroll_date,expiry_date,id_no,
nationality,sub_type,vip_level,member_source,duty_code,language_code,member_department_code,id_type,
chname,address,engname,'b1e79b6670e5fce34747b8c0a0e684beba9b8550','Shiji','2018-01-11'
from member where card_no not in (select card_no from member_info_svc);
update member_info_svc set state='N' where insert_date='2018-01-11' and insert_user='Shiji'
将新导入的会员插入使用者表
insert into member_info_sub(parent_gh_no,sub_name,sex,title,birthday,nationality,address,tel,fax,email,remarks,create_date,position_code,language_code,valid_flag,exchange_flag,filter_flag,
main_flag,link_code,member_department_code,id_type,id_no,engname,office_tel,mobile,card_sn,card_no)
select gh_no,chname,sex,title,birthday,nationality,address,tel,fax,email,memo,enroll_date,duty_code,language_code,'1','1','0',
'1','0',member_department_code,id_type,id_no,engname,office_tel,office_tel,card_sn,card_no
from member_info_svc
where insert_user='Shiji' and insert_date='2018-01-11'
and not exists(select parent_gh_no from member_info_sub where parent_gh_no=member_info_svc.gh_no)
插入变更记录
-------------------------------------------------member_info_log-------------------------------------------------------
insert into member_card_modify_log( gh_no,oper_type,oper_date,operator_id,remark )
select gh_no,'N',getdate(),'admin','20180111批量导入'
from member_info_svc
where insert_user='Shiji'
and not exists (select gh_no from member_card_modify_log where gh_no=member_info_svc.gh_no)
导入模板样式
代码语言：javascript复制
card_no chname first_name last_name title sex birthday email linkman_tel tel office_tel fax address zip id_type id_no language_code nationality member_department_code duty_code membership_type vip_level sub_type member_source enroll_date expiry_date balance sendtype_code memo
960000016 徐新革 gexin xu Mr M 1985-12-31 CRO@SHIJINET.CN 12345678900 0001 123456789000000000 C CN DEA AA HZYHZZK 0 FOR HZ133001GM 2019-07-10 2059-07-10 200 CZ
插入UUID
代码语言：javascript复制
insert into member_info_svc(gh_no) values((select replace(NEWID(),'-','')))
获取UUID
代码语言：javascript复制
select (replace(NEWID(),'-',''))
可生成UUID版本
代码语言：javascript复制
--从临时表插入会员至member_info_svc表
insert into member_info_svc (
gh_no,chname,sex,birthday,tel,email,title,card_no,membership_type,enroll_date,expiry_date,id_no,
nationality,sub_type,vip_level,member_source,duty_code,language_code,member_department_code,id_type,
linkman_name,address,engname,card_password,insert_user,insert_date)
--生成UUID导入
select (replace(NEWID(),'-','')),chname,sex,birthday,tel,email,title,card_no,membership_type,enroll_date,expiry_date,id_no,
nationality,sub_type,vip_level,member_source,duty_code,language_code,member_department_code,id_type,
chname,address,engname,'b1e79b6670e5fce34747b8c0a0e684beba9b8550','Shiji','2018-01-11'
from member where card_no not in (select card_no from member_info_svc);
update member_info_svc set state='N' where insert_date='2018-01-11' and insert_user='Shiji'
--将新导入的会员插入使用者表
insert into member_info_sub(parent_gh_no,sub_name,sex,title,birthday,nationality,address,tel,fax,email,remarks,create_date,position_code,language_code,valid_flag,exchange_flag,filter_flag,
main_flag,link_code,member_department_code,id_type,id_no,engname,office_tel,mobile,card_sn,card_no)
select gh_no,chname,sex,title,birthday,nationality,address,tel,fax,email,memo,enroll_date,duty_code,language_code,'1','1','0',
'1','0',member_department_code,id_type,id_no,engname,office_tel,office_tel,card_sn,card_no
from member_info_svc
where insert_user='Shiji' and insert_date='2018-01-11'
and not exists(select parent_gh_no from member_info_sub where parent_gh_no=member_info_svc.gh_no)
--插入变更记录
-------------------------------------------------member_info_log-------------------------------------------------------
insert into member_card_modify_log( gh_no,oper_type,oper_date,operator_id,remark )
select gh_no,'N',getdate(),'admin','20180111批量导入'
from member_info_svc
where insert_user='Shiji'
and not exists (select gh_no from member_card_modify_log where gh_no=member_info_svc.gh_no)
代码语言：javascript复制
--想要将值插入到自动编号（或者说是标识列，IDENTITY）中去，需要设定
SET IDENTITY_INSERT member_info_svc ON
SET IDENTITY_INSERT member_info_svc off
