CREATE TABLE TImpCardType ( FId BIGINT, FNo VARCHAR(20), FName VARCHAR(30) ) INSERT TImpCardType SELECT dbo.FNNewId(),卡名称,卡名称 FROM TImpMember GROUP BY 卡名称 ALTER TABLE TImpMember ADD FMemId BIGINT ALTER TABLE TImpMember ADD FCardNo VARCHAR(50) ALTER TABLE TImpMember ADD FMemNo VARCHAR(50) ALTER TABLE TImpMember ADD FName VARCHAR(50) ALTER TABLE TImpMember ADD FGender INT ALTER TABLE TImpMember ADD FMobile VARCHAR(50) ALTER TABLE TImpMember ADD FCardTypeId BIGINT ALTER TABLE TImpMember ADD FCourseId BIGINT ALTER TABLE TImpMember ADD FBalance DECIMAL(18,6) ALTER TABLE TImpMember ADD FBalance2 DECIMAL(18,6) ALTER TABLE TImpMember ADD FRemainTimes INT ALTER TABLE TImpMember ADD FPurTimes INT ALTER TABLE TImpMember ADD FPurAmount DECIMAL(18,6) ALTER TABLE TImpMember ADD FItemId BIGINT ALTER TABLE TImpMember ADD FCreateDate DATETIME ALTER TABLE TImpMember ADD FExpiredDate DATETIME ALTER TABLE TImpMember ADD FCompId BIGINT ALTER TABLE TImpMember ADD FCardId BIGINT ALTER TABLE TImpMember ADD FMemo VARCHAR(1000) ALTER TABLE TImpMember ADD FArrear DECIMAL(18,6) ALTER TABLE TImpMember ADD FPoint DECIMAL(18,6) ALTER TABLE TImpMember ADD FBirthday DATETIME ALTER TABLE TImpMember ADD FBirthdayType CHAR select * from TImpMember UPDATE TImpMember SET FMemId = dbo.FNNewId() WHERE FMemid IS NULL UPDATE TImpMember SET FCardId = dbo.FNNewId() WHERE FCardId IS NULL UPDATE TImpMember SET FCardNo = 卡号 UPDATE TImpMember SET FMemNo = FCardNo UPDATE TImpMember SET FName = 姓名 UPDATE TImpMember SET FMobile = 手机 UPDATE TImpMember SET FGender = CASE WHEN 性别 = '男' THEN 1 ELSE '0' END UPDATE TImpMember SET FBalance = replace(卡内余额,'元','') UPDATE TImpMember SET FBalance2 = replace(赠送余额,'元','') UPDATE TImpMember SET FCreateDate = 注册日期 UPDATE TImpMember SET FExpiredDate = 失效日期 WHERE LEN(失效日期) = 10 UPDATE TImpMember SET FArrear = 欠款 UPDATE TImpMember SET FMemo = 备注 UPDATE TImpMember SET FPoint = replace(当前积分,'分','') UPDATE TImpMember SET FCompId = 1 UPDATE A SET A.FCardTypeId = B.FId FROM TImpMember AS A,TImpCardType AS B WHERE A.卡名称= B.FName UPDATE TImpMember SET FExpiredDate = DATEADD(YEAR,100,FCreateDate) WHERE FExpiredDate IS NULL UPDATE TImpMember SET FBirthdayType = 'G' UPDATE TImpMember SET FBirthdayType = 'L' WHERE 生日 LIKE '%农历%' UPDATE TImpMember SET FBirthday = REPLACE(REPLACE(生日 , '农历','9999-') , '阳历','9999-') SELECT * FROM TImpMember WHERE FMemId = 6273693882209312287 UPDATE TImpMember SET FCardNo = 卡号 ,FMemNo = 卡号 WHERE FMemId = 6273693882209312287 UPDATE TImpMember SET FCardNo = 卡号 + '.2' ,FMemNo = 卡号 + '.2' WHERE FMemId = 8349508648302214543 UPDATE TImpMember SET FMemId = 6273693882209312287 WHERE FMemId = 8349508648302214543 UPDATE A SET A.FCardNo = A.FMobile, A.FMemno = A.FMobile from TImpMember AS A WHERE FMemNo IN ( SELECT FMemNo FROM ( SELECT FMemNo,SUM(1) AS Cnt FROM TImpMember GROUP BY FMemNo ORDER BY SUM(1) DESC ) AS T WHERE T.Cnt > 1 ) SELECT * FROM TImpMember WHERE FMemno = '10839' 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 ,FNo,FName,1,1,0,0,3,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 FROM TImpCardType UPDATE TMemberCardType SET FMaterial = 2 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, ISNULL(FCreateDate ,'1900-01-01') 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 TImpMember WHERE FCardId != 7186394874210754547 INSERT INTO TMemberCard( FCardId, FCardNo, FCardFaceNo, FTypeId, FValueType, FCompId, FMemId, FState, FCreateDate, FExpiredDate, FTotalTimes, FUsedTimes, FSaleCompId, FSaleBillId, FMemo) SELECT FCardId AS FId, FMemno AS FNo, FMemno AS FFaceNo, FCardTypeId AS FTypeId, (SELECT FValueType FROM TMemberCardType WHERE FId = FCardTypeId) AS FValueType, FCompId AS FCompId, FMemId AS FMemId, 1 AS FState, ISNULL(FCreateDate,1900) AS FCreateDate, DATEADD(year,100, ISNULL(FCreateDate,2000)) AS FExpiredDate, 0 AS FTotalTimes, 0 AS FUsedTimes, FCompId AS FSaleCompId, 0 AS FSaleBillId, '' AS FMemo FROM TImpMember WHERE FCardTypeId IS NOT NULL INSERT TMemberCardAccount(FMemId,FId,FCardId,FAcctId,FDeposit,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo) SELECT FMemId,dbo.FNNewId(),FCardId AS FCardId,3,FBalance,FBalance,0,FCreateDate,DATEADD(year,1, FCreateDate) AS FExpiredDate,'' FROM TImpMember AS A WHERE FCardTypeId IS NOT NULL 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 TImpMember AS A WHERE FCardTypeId IS NOT NULL INSERT TMemberCardAccount(FMemId,FId,FCardId,FAcctId,FDeposit,FBalance,FArrear,FCreateDate,FExpiredDate,FMemo) SELECT FMemId,dbo.FNNewId(),FCardId AS FCardId,10,FBalance2,FBalance2,0,FCreateDate,DATEADD(year,1, FCreateDate) AS FExpiredDate,'' FROM TImpMember AS A WHERE FBalance2 != 0 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,10,getdate(),A.FCardTypeId,A.FCompId,'DR','Import',0,'',A.FBalance2,0,A.FBalance2,0,getdate(),'从Excel中导入' FROM TImpMember AS A WHERE FBalance2 != 0 SELECT * FROM TImpMember AS A WHERE FBalance2 != 0