[昆仑会员卡系统]老会员数据导入 从临时表插入会员至member_info_svc表 SQL

 

 

• 第一版无UUID版本

 

从临时表插入会员至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)

• 导入模板样式

 

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

insert into member_info_svc(gh_no) values((select replace(NEWID(),'-','')))

 

• 获取UUID

select (replace(NEWID(),'-',''))

 

 

 

 

•  可生成UUID版本

 

 

--从临时表插入会员至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)

 

 

--想要将值插入到自动编号（或者说是标识列，IDENTITY）中去，需要设定　

SET IDENTITY_INSERT   member_info_svc ON

SET IDENTITY_INSERT   member_info_svc off