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

[landv]

现在只能作为参考值了，asp版本系统切换为C# .net 版本了

-- 第一版无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

20200421测试库-会员余额积分报告更新脚本。member_card_fee需要初始化续费记录。

DECLARE @gh_no VARCHAR(200)--声明游标变量
DECLARE @card_no VARCHAR(200)--声明游标变量（卡号
DECLARE @balance VARCHAR(200)--声明游标变量（余额

DECLARE curfuntioncode CURSOR FOR SELECT mis.gh_no,mis.card_no,m.balance FROM member m
inner join member_info_svc mis on m.card_no = mis.card_no and m.balance > 0 --创建游标

OPEN curfuntioncode --打开游标
FETCH NEXT FROM curfuntioncode INTO @gh_no,@card_no,@balance --给游标变量赋值
WHILE @@FETCH_STATUS=0 --判断FETCH语句是否执行执行成功
BEGIN 

insert into member_card_fee (card_no,gh_no,dt,fee,oper_type,last_balance,this_balance,
operator_id,payment,remark,insert_user,insert_date,update_user,update_date,place_code)
values(@card_no,@gh_no,'2019-07-01 00:00:00',@balance,'T',0,@balance,'ShiJi','CHUSHIHUA','Transaction：TopUp；Card No.'+@card_no+'；Remark：'+@balance,
'ShiJi',GETDATE(),'ShiJi',GETDATE(),'CSH130101')

FETCH NEXT FROM curfuntioncode INTO @gh_no,@card_no,@balance --下一个游标变量赋值
END 
CLOSE curfuntioncode --关闭游标
DEALLOCATE curfuntioncode --释放游标