drop table member_type2 SELECT * FROM TImpMember2 SELECT * INTO TImpMember2 FROM qd77.beijing_jianfa_old2.dbo.clients SELECT * INTO member_type2 FROM qd77.beijing_jianfa_old2.dbo.member_type2 delete TImpMember2 where 编码 ='0' SELECT * FROM member_type2 SELECT * FROM TMemberCardType INSERT INTO TMemberCardType(FId,FNo,FName,FValueType,FMaterial,FPrice,FCardFeeAmt,FAcctId,FDeposit,FAcctId2,FDeposit2,FAcctId3,FDeposit3,FTimes,FAlertBalance,FMinRchgAmt,FSalePointMode,FSalePointValue,FRchgPointMode,FRchgPointValue,FPaySmsFee,FTimeCard,FValidityPeriod,FValidityPeriodUnit,FExpiredDate,FDefaultMemo,FCreateTime,FAllowSale,FAllowRchg,FDeptId,FEnabled,FOrder) SELECT FId =20000 + type_id, 'G-' + type_no,'G-' + type_name as name,1,FMaterial = 2,sale_price,FCardFeeAmt = 0,FAcctId = 3,FDeposit = 0,FAcctId2 = 0,FDeposit2 = 0 ,FAcctId3 = 0,FDeposit3 = 0, FTimes = 0,FAlertBalance = 0,FMinRchgAmt = 0,FSalePointMode = 0,FSalePointValue = 0 ,FRchgPointMode = 0,FRchgPointValue = 0,FPaySmsFee = 0,FTimeCard = 0,FValidityPeriod = 100,FValidityPeriodUnit = 1, FExpiredDate = NULL,FDefaultMemo = '',FCreateTime = GETDATE(),1,1,FDeptId = 0,FEnabled =1,FOrder = 1 FROM member_type2 INSERT INTO TMemberCardTypeSub(FId,FCompId,FCardTypeId,FAllowSale,FAllowRchg,FEnabled) SELECT dbo.FNNewId(),C.FId,I.FId,FAllowSale,FAllowRchg,FEnabled FROM TMemberCardType AS I,TCompany AS C WHERE I.FId > 1 AND C.FId IN (dbo.FNCompId('009'),1) AND I.FNo LIKE 'G%' ALTER TABLE TImpMember2 ADD FCardTypeName VARCHAR(100) ALTER TABLE TImpMember2 ADD FMemId BIGINT ALTER TABLE TImpMember2 ADD FMemNo VARCHAR(50) ALTER TABLE TImpMember2 ADD FCardNo VARCHAR(50) ALTER TABLE TImpMember2 ADD FName VARCHAR(50) ALTER TABLE TImpMember2 ADD FGender INT ALTER TABLE TImpMember2 ADD FCardState INT ALTER TABLE TImpMember2 ADD FMobile VARCHAR(50) ALTER TABLE TImpMember2 ADD FCardTypeId BIGINT ALTER TABLE TImpMember2 ADD FCourseId BIGINT ALTER TABLE TImpMember2 ADD FBalance DECIMAL(18,6) ALTER TABLE TImpMember2 ADD FBalance2 DECIMAL(18,6) ALTER TABLE TImpMember2 ADD FRemainTimes INT ALTER TABLE TImpMember2 ADD FPurTimes INT ALTER TABLE TImpMember2 ADD FPurAmount DECIMAL(18,6) ALTER TABLE TImpMember2 ADD FItemId BIGINT ALTER TABLE TImpMember2 ADD FCreateDate DATETIME ALTER TABLE TImpMember2 ADD FExpiredDate DATETIME ALTER TABLE TImpMember2 ADD FCompId BIGINT ALTER TABLE TImpMember2 ADD FCardId BIGINT ALTER TABLE TImpMember2 ADD FMemo VARCHAR(1000) ALTER TABLE TImpMember2 ADD FArrear DECIMAL(18,6) ALTER TABLE TImpMember2 ADD FPoint DECIMAL(18,6) ALTER TABLE TImpMember2 ADD FState INT ALTER TABLE TImpMember2 ADD FBirthday DATETIME ALTER TABLE TImpMember2 ADD FBirthdayType CHAR UPDATE A SET A.FCardTypeId = 20000 + member_typeid FROM TImpMember2 AS A SELECT* FROM TImpMember2 WHERE FCardTypeId IS NULL UPDATE TImpMember2 SET FCardTypeId = 5424538041950704798 WHERE member_typeid is null DELETE TImpMember2 WHERE 编码 is null SELECT * FROM TImpMember2 UPDATE TImpMember2 SET FMemId = 30000 + client_id UPDATE TImpMember2 SET FCardid = 40000 + client_id UPDATE TImpMember2 SET FMemNo = client_no UPDATE TImpMember2 SET FCardNo = client_no UPDATE TImpMember2 SET FName = client_name UPDATE TImpMember2 SET FMobile = mobil_phone UPDATE TImpMember2 SET FCreateDate = create_date UPDATE TImpMember2 SET FExpiredDate = 截止日期 UPDATE TImpMember2 SET FExpiredDate = DATEADD(YEAR,100,create_date) UPDATE TImpMember2 SET FGender = CASE WHEN sex= 'M' THEN 1 ELSE 0 END UPDATE TImpMember2 SET FBirthdayType = 'G' UPDATE TImpMember2 SET FCardState = 1 UPDATE TImpMember2 SET FBalance = remain_amount UPDATE TImpMember2 SET FPoint = total_cent select * from syscolumns where id = object_id('TImpMember2') and name like '%CATE%' UPDATE TImpMember2 SET FMemo = 备注 UPDATE TImpMember2 SET FCompId = dbo.FNCompID('009') SELECT * FROM TImpMember2 WHERE FMemNo = '660009' --门店 UPDATE A SET A.FCompId = B.FId FROM TImpMember2 AS A,TCompany AS B WHERE 记帐门店 = B.FCompName UPDATE TImpMember2 SET FCompID = 1 UPDATE TImpMember2 SET FMemNo = 'G' + FMemNo WHERE FMemNo IN(SELECT FMemNO FROM TMember) UPDATE TImpMember2 SET FCardNo = 'G' + FCardNo WHERE FCardNo IN(SELECT FCardNo FROM TMemberCard) INSERT INTO TMember( FMemId, FMemNo, FName, FBriefCode, FTypeId, FCompId, FCreateDate, FGender, FBirthdayType, FBirthday,FTelephone,FMobilePhone,FQQ,FWeChat,FIdCard,FEmail,FAddress,FProId,FCPW,FQPW, FChargeEmpId,FPoint,FRecvConsSMS,FRecvBulkSMS,FMemo,FCustom1,FCustom2,FCustom3,FCustom4,FCustom5) SELECT FMemId AS FMemId, FMemNo AS FMemNo, FName AS FName, dbo.FNGetFirstPinYin(FName), 0 AS FTypeId, FCompId AS FCompId, FCreateDate AS FCreateDate, FGender AS FGender, FBirthdayType AS FBirthdayType, FBirthday AS FBirthday, '' AS FTelephone, ISNULL(FMobile,'') AS FMobilePhone, '' AS FQQ, '' AS FWeChat, '' AS FIdCard, '' AS FEmail, '' AS FAddress, 0 AS FProId, '' AS FCPW, '' AS FQPW, 0 AS FChargeEmpId, isnull(FPoint,0) AS FPoint, 1 AS FRecvConsSMS, 1 AS FRecvBulkSMS, ISNULL(FMemo,'') AS FMemo, '' AS FCustom1,'' AS FCustom2,'' AS FCustom3,'' AS FCustom4,'' AS FCustom5 from TImpMember2 WHERE FMemNO IN(SELECT FMemNO FROM TMember) INSERT TMemberPointHist(FId,FMemId,FTime,FCardId,FCompId,FBillType,FBizType,FTransId,FTransNo,FInAmount,FOutAmount,FBalance,FMemo,FRecordTime) SELECT dbo.FNNewId(),A.FMemId,getdate(),0,1,'DR','Import',0,'',FPoint,0,FPoint,'',getdate() FROM TImpMember2 AS A WHERE FPoint != 0 select * from tbasictype where fkey = 'GoodsSaleWorkType' INSERT INTO TMemberCard( FCardId, FCardNo, FTypeId, FValueType, FCompId, FMemId, FState, FCreateDate, FExpiredDate, FTotalTimes, FUsedTimes, FSaleCompId, FSaleBillId, FMemo) SELECT FCardId AS FId, FCardNo AS FNo, FCardTypeId AS FTypeId, (SELECT FValueType FROM TMemberCardType WHERE FId = FCardTypeId) AS FValueType, FCompId AS FCompId, FMemId AS FMemId, FCardState AS FState, FCreateDate AS FCreateDate, DATEADD(year,1, FCreateDate) AS FExpiredDate, 0 AS FTotalTimes, 0 AS FUsedTimes, FCompId AS FSaleCompId, 0 AS FSaleBillId, ISNULL('','') AS FMemo FROM TImpMember2 WHERE FCardNo NOT IN(SELECT FCardNo FROM TMemberCard) INSERT TMemberCardAccount(FMemId,FId,FCardId,FAcctId,FDeposit,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo) SELECT FMemId,dbo.FNNewId(),FCardId AS FCardId,3,FBalance,FBalance,0,FCreateDate,NULL AS FExpiredDate,'' FROM TImpMember2 AS A WHERE FCardId IN(SELECT FCardId FROM TMemberCard) INSERT TMemberCardAcctHist(FId,FMemId,FCardId,FAcctId,FTime,FCardTypeId,FCompId,FBillType,FBizType,FTransId,FTransNo,FInAmount,FOutAmount,FBalance,FArrear,FRecordTime,FMemo) select dbo.FNNewId(),A.FMemId,A.FCardId,3,getdate(),A.FCardTypeId,A.FCompId,'DR','Import',0,'',A.FBalance,0,A.FBalance,0,getdate(),'从Excel中导入' FROM TImpMember2 AS A WHERE FCardId IN(SELECT FCardId FROM TMemberCard) INSERT TMemberOldTransHist(FId,FMemId,FTime,FCardId,FCompId,FBillType,FBizType,FTransNo,FContent,FPrice,FQuantity,FAmount,FPayment,FEmployee,FMemo) SELECT dbo.FNNewId(),M.FMemId,b.app_date,0,C.FId,'DR','Import',a.ticket_id,inventory.ps_name,a.discount_amount,1,a.amount,'',employees,'' FROM qd77.beijing_jianfa_old.dbo.tickets_detail AS A left join qd77.beijing_jianfa_old.dbo.inventory on a.ps_seq = inventory.ps_seq , qd77.beijing_jianfa_old2.dbo.tickets AS B,TImpMember2 AS M,qd77.beijing_jianfa_old2.dbo.categoryes AS C2,TCompany AS C WHERE ( b.ticket_id = a.ticket_id ) and ( -- ( tickets.client_id = 1006 ) AND ( b.pay_mark = 'Y' ) AND ( b.status = 'F' ) AND --( tickets_detail.ps_type = 'P' ) AND ( b.end_date >= '2000-4-30 0:0:0.000' ) AND ( b.end_date <= '2222-4-30 23:59:59.999' ) ) --AND ( tickets_detail.sale_type = '1' ) ) AND M.client_id = b.client_id AND C2.category_id = b.category_id AND C2.category_name = C.FCompName INSERT TMemberOldTransHist(FId,FMemId,FTime,FCardId,FCompId,FBillType,FBizType,FTransNo,FContent,FPrice,FQuantity,FAmount,FPayment,FEmployee,FMemo) SELECT dbo.FNNewId(),M.FMemId,b.app_date,0,dbo.FNCompId('009'),'DR','Import',a.ticket_id,isnull(inventory.ps_name,''),a.discount_amount,1,a.amount,'',employees,'' FROM qd77.beijing_jianfa_old2.dbo.tickets_detail AS A left join qd77.beijing_jianfa_old2.dbo.inventory on a.ps_seq = inventory.ps_seq , qd77.beijing_jianfa_old2.dbo.tickets AS B,TImpMember2 AS M WHERE ( b.ticket_id = a.ticket_id ) and ( -- ( tickets.client_id = 1006 ) AND ( b.pay_mark = 'Y' ) AND ( b.status = 'F' ) AND --( tickets_detail.ps_type = 'P' ) AND ( b.end_date >= '2000-4-30 0:0:0.000' ) AND ( b.end_date <= '2222-4-30 23:59:59.999' ) ) --AND ( tickets_detail.sale_type = '1' ) ) AND M.client_id = b.client_id use jianfa SELECT * FROM TMemberCardTypeCross INSERT TMemberCardTypeCross(FId,FCompId,FCardKind,FCardTypeId,FCrossCompId,FValue) SELECT dbo.FNnEWid(),dbo.FNCompId('009'),'Money',C.FId,9,0 FROM TMemberCardType AS C WHERE C.FId IN(SELECT FCardTypeId FROM TimpMember UNION ALL SELECT FCardTypeId FROM TImpMember2) INSERT TMemberCardTypeCross(FId,FCompId,FCardKind,FCardTypeId,FCrossCompId,FValue) SELECT dbo.FNnEWid(),9,'Money',C.FId,dbo.FNCompId('009'),0 FROM TMemberCardType AS C WHERE C.FId IN(SELECT FCardTypeId FROM TimpMember UNION ALL SELECT FCardTypeId FROM TImpMember2)