DELETE FROM dbo.FilesManagement DELETE FROM dbo.Score_JournalEvaluation DELETE FROM dbo.FC_Score DELETE FROM dbo.FC_SESRelatedData DELETE FROM [dbo].[FC_SESReport] DELETE FROM [dbo].[FC_SESReportToCPT] DELETE FROM [dbo].[SESList] DELETE FROM [dbo].[CPTList] DELETE FROM [dbo].[SSR] ----DELETE FROM dbo.Sys_UserToEMial --==注：必须先把原表里面用户信息拉取过来后才可以进行数据抓取，角色相关信息按照客户要求进行新增==-- --==注：确保数据插入表为空才可以执行，只可执行一次，单条执行，切记别统一执行==-- --==脚本单条执行，需为空库，切记不可拿有数据的库进行导入，目前只有业务相关数据，必须先把原表的用户信息全部拉取过来后才可以进行执行 --==小星星评价==-- truncate table FCLDB..FC_Score insert into FCLDB..FC_Score(Contract_No,Score1,Score2,Score3,Score4,Score5,Score6,Score7, Timely,Honesty,UserId,[Role],FileID,Remark,Remark1,DateIn,UserName,RoleId) select s.Contract_No,s.Score1,s.Score2,s.Score3,s.Score4,s.Score5,s.Score6,s.Score7, 0 Timely,0 Honesty, (CASE WHEN (SELECT u.UserId FROM dbo.Sys_User u WHERE u.Account=s.[By]) IS NULL THEN '' ELSE (SELECT u.UserId FROM dbo.Sys_User u WHERE u.Account=s.[By]) END) UserId, s.[Role],s.FileID,s.Remark,s.Remark1,s.DateIn,s.[By] UserName,'' RoleId FROM CPT..FC_Score as s --附件 insert into FCLDB..FilesManagement(FileId,FO,FileName,FileType,FileLength,UploadUser,UploadDate,Remark,FileUrl) select NewId() FileId,f.Contract_NO FO,f.[FileName],f.FileType,f.FileLength,f.UploadUser,f.DateIn UploadDate,f.Remark,'File/'+f.FileID FROM CPT..FC_File as f --INNER join CPT..FC_Score as s on f.Contract_NO=s.Contract_No --评价日志 truncate table FCLDB..Score_JournalEvaluation insert into FCLDB..Score_JournalEvaluation(SES_No,Evaluate_UserId,Evaluate_UserName,Evaluate_Time,Contract_No,Role,Remark) SELECT a.SES_No,(SELECT u.UserId FROM dbo.Sys_User u WHERE u.Account=a.[By]) Evaluate_UserId, [By] Evaluate_UserName,DateIn Evaluate_Time,a.Contract_No,a.[Role],a.Remark FROM CPT..FC_SESRecord as a --==基础信息==-- --Base_Contractor --insert into FCLDB..Base_Contractor(ContractorId,Contractor,VendorNumber) --select NEWID() ContractorId,Contractor,Vendor_NO -- FROM CPT..FC_SESRelatedData where isnull(Vendor_NO,'')!='' -- GROUP by Contractor,Vendor_NO --Base_PriceScheme --insert into FCLDB..Base_PriceScheme(PriceSchemeId,PriceScheme) --select NEWID() PriceSchemeId,Pricing_Scheme from CPT..FC_SESRelatedData where ISNULL(Pricing_Scheme,'')!='' group by Pricing_Scheme --Base_Item --insert into FCLDB..Base_Item(ItemId,Item) --select NEWID() as ItemId,Item from CPT..FC_SESRelatedData where ISNULL(Item,'')!='' group by Item --Base_Currency --insert into FCLDB..Base_Currency(CurrencyId,Currency) --select NEWID() CurrencyId,Currency from CPT..FC_SESRelatedData where isnull(Currency,'')!='' group by Currency --Base_Type --insert into FCLDB..Base_Type(TypeId,[Type]) --select NEWID(),[Type] from CPT..FC_SESRelatedData where isnull([Type],'')!='' group by [Type] --GO --在提取FC_SESRelatedData数据时选更新不规范的CPT UPDATE CPT..FC_SESRelatedData SET Vendor_NO='2315246' WHERE Vendor_NO='2520280' GO UPDATE CPT..FC_SESRelatedData SET Vendor_NO='0000000' WHERE Vendor_NO='' GO UPDATE CPT..FC_SESRelatedData SET Vendor_NO='2306803' WHERE Vendor_NO='On-call' GO UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Cui GuangMing' WHERE Main_Coordinator='Cui Guangmin' UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Hou yingyan' WHERE Main_Coordinator='Houyingyan' UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='wu jiazhao' WHERE Main_Coordinator='wu jiazhao Yang Gang' UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Ben guoping' WHERE Main_Coordinator='Ben guopin' UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Dong LuJin' WHERE Main_Coordinator='Donglujin' UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Jiang Peng' WHERE Main_Coordinator='jiangpeng' UPDATE CPT..FC_SESRelatedData SET Main_Coordinator='Jiang Peng' WHERE Main_Coordinator='jiangpeng' UPDATE CPT..FC_SESRelatedData SET User_Representative='Tong ShaoJian' WHERE User_Representative='Tong Shaojian' UPDATE CPT..FC_SESRelatedData SET User_Representative='Xu PanPan' WHERE User_Representative='Xu Panpan' UPDATE CPT..FC_SESRelatedData SET User_Representative='Cui GuangMing' WHERE User_Representative='Cui Guangmin' UPDATE CPT..FC_SESRelatedData SET User_Representative='Wen WeiPing' WHERE User_Representative='Wen Weipng' UPDATE CPT..FC_SESRelatedData SET User_Representative='' WHERE User_Representative='liyinghua' UPDATE CPT..FC_SESRelatedData SET User_Representative='' WHERE User_Representative='N/A' GO --FC_SESRelatedData truncate table FCLDB.dbo.FC_SESRelatedData insert into FCLDB..FC_SESRelatedData(FO_NO, Contract_Title, Contractor, Pricing_Scheme, Item, Material_Group, Purchase_Group, Cost_Element, Vendor_NO, Currency, Original_WC, Type, Contract_Admin, Buyer, Main_Coordinator, User_Representative, Applicant, Validate_Date, Expire_Date, FC_Status, Contract_Person, Contract_Tel, Total_Budget, Proportion_of_FC_Definition, Actual_Budget, DateIn, Remark, Remark1, OldSAPData, ConstRecords) SELECT a.FO_NO, a.Contract_Title, (CASE WHEN a.Vendor_NO!='0000000' THEN (select top 1 c.ContractorId from Base_Contractor c where c.VendorNumber=a.Vendor_NO) ELSE (select top 1 c.ContractorId from Base_Contractor c where charindex(rtrim(ltrim(c.ContractorCN)),a.Contractor)>0 OR charindex(rtrim(ltrim(c.Contractor)), a.Contractor)>0) END) AS Contractor, p.PriceSchemeId as FC_Price_Scheme, i.ItemId, a.Material_Group, a.Purchase_Group, a.Cost_Element, a.Vendor_NO, c.CurrencyId, a.Original_WC, t.TypeId, --ca.UserId as Contract_Admin, (SELECT TOP 1 u.UserId FROM dbo.Sys_User u WHERE u.UserName= a.Contract_Admin ) as Contract_Admin, a.Buyer, --mc.UserId as Main_Coordinator, --ur.UserId as User_Representative, (SELECT TOP 1 u.UserId FROM dbo.Sys_User u WHERE u.UserName= a.Main_Coordinator) as Main_Coordinator, (SELECT TOP 1 u.UserId FROM dbo.Sys_User u WHERE LOWER(u.UserName)= LOWER(a.User_Representative)) as User_Representative, a.Applicant, a.Validate_Date, a.Expire_Date, a.FC_Status, a.Contract_Person, a.Contract_Tel, a.Total_Budget, a.Proportion_of_FC_Definition, a.Actual_Budget, a.DateIn, a.Remark, a.Remark1, a.OldSAPData, 'N' as ConstRecords from CPT..FC_SESRelatedData as a left join FCLDB..Base_PriceScheme as p on p.PriceScheme = a.Pricing_Scheme left join FCLDB..Base_Item as i on i.Item = a.Item left join FCLDB..Base_Currency as c on c.Currency = a.Currency left join FCLDB..Base_Type as t on t.Type = a.Type GO --==FC_SESReport truncate table FCLDB.dbo.FC_SESReport insert into FCLDB.dbo.FC_SESReport(SES_No,Short_Descrption,Start_Date,End_Date,Created_by, Created_on,TECO_Date,TECO_Format,Requisitioner,FO,Item,Vendor_Name,Discipline, SSR_budget,Currency,Contractor_quotation,SSR_Actual_cost,Cost_checker,Tax_rate, Changed_by,Deviation,Deviation_Percentage,Overrun,Long_text,Work_Order,Function_location, Main_work_center,Work_Center,Dep,Section,Cost_center,WBS,Network,TODAY,Claim_sheets_receive, CS_REC_Format,No_SUBM_To_today,Contractor_duration,Engineer_confirmed_o,ENG_CONF_Format, No_BoQ_CONF_to_today,BoQ_confirmation_dur,SES_Confirmed_on,SES_CONF_Format,No_SES_to_today, Settlement_duration,Invoiced_on,Invoice_duration,Payment_made_on,Payment_duration,DateIn, Remark1,Remark2,Accepted,Deleted,Blocked) SELECT SES_No,Short_Descrption, (case ISDATE((case ISNULL(Start_Date,'') when '' then null ELSE ((case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,7,4) end) +'-'+(case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,4,2) end) +'-'+(case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,1,2) end)) end)) WHEN 1 then (case ISNULL(Start_Date,'') when '' then null else ((case ISNULL(Start_Date,'') WHEN '' then '' else SUBSTRING(Start_Date,7,4) end) +'-'+(case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,4,2) end) +'-'+(case ISNULL(Start_Date,'') when '' then '' else SUBSTRING(Start_Date,1,2) end)) end) ELSE null end) as Start_Date, (case ISDATE((case ISNULL(End_Date,'') when '' then null ELSE ((case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,7,4) end) +'-'+(case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,4,2) end) +'-'+(case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,1,2) end)) end)) WHEN 1 then (case ISNULL(End_Date,'') when '' then null ELSE ((case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,7,4) end) +'-'+(case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,4,2) end) +'-'+(case ISNULL(End_Date,'') when '' then '' else SUBSTRING(End_Date,1,2) end)) end) ELSE null end) as End_Date, Created_by, (case ISDATE((case ISNULL(Created_on,'') when '' then null ELSE ((case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,7,4) end) +'-'+(case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,4,2) end) +'-'+(case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,1,2) end)) end)) WHEN 1 then (case ISNULL(Created_on,'') when '' then null ELSE ((case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,7,4) end) +'-'+(case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,4,2) end) +'-'+(case ISNULL(Created_on,'') when '' then '' else SUBSTRING(Created_on,1,2) end)) end) ELSE null end) as Created_on, TECO_Format as TECO_Date,TECO_Format,Requisitioner,FO,Item,Vendor_Name,Discipline, (case ISNUMERIC((case ISNULL(SSR_budget,'') when '' then null ELSE REPLACE(REPLACE(SSR_budget, '.', ''),',','.') end)) WHEN 1 then (case ISNULL(SSR_budget,'') when '' then null ELSE REPLACE(REPLACE(SSR_budget, '.', ''),',','.') end) ELSE null end) SSR_budget, Currency, (case ISNULL(Contractor_quotation,'') when '' then null else Contractor_quotation end) as Contractor_quotation, (case ISNUMERIC((case ISNULL(SSR_budget,'') when '' then null ELSE REPLACE(REPLACE(SSR_Actual_cost, '.', ''),',','.') end)) WHEN 1 then (case ISNULL(SSR_budget,'') when '' then null ELSE REPLACE(REPLACE(SSR_Actual_cost, '.', ''),',','.') end) ELSE null end) as SSR_Actual_cost, Cost_checker, (case ISNULL(Tax_rate,'') when '' then null else Tax_rate end) as Tax_rate, Changed_by, (case ISNUMERIC((case ISNULL(Deviation,'') when '' then null ELSE REPLACE(REPLACE(Deviation, '.', ''),',','.') end)) WHEN 1 then (case ISNULL(Deviation,'') when '' then null ELSE REPLACE(REPLACE(Deviation, '.', ''),',','.') end) else null end) AS Deviation, (case ISNUMERIC((case ISNULL(Deviation_Percentage,'') when '' then null ELSE REPLACE(REPLACE(Deviation_Percentage, '.', ''),',','.') end)) WHEN 1 then (case ISNULL(Deviation_Percentage,'') when '' then null ELSE REPLACE(REPLACE(Deviation_Percentage, '.', ''),',','.') end) else null end) AS Deviation_Percentage, Overrun,Long_text,Work_Order,Function_location,Main_work_center, Work_Center,Dep,Section,Cost_center,WBS,Network, (case ISDATE(TODAY) when 1 then TODAY else null end) as TODAY, CS_REC_Format as Claim_sheets_receive, CS_REC_Format,No_SUBM_To_today,Contractor_duration, ENG_CONF_Format as Engineer_confirmed_o,ENG_CONF_Format,No_BoQ_CONF_to_today, BoQ_confirmation_dur,SES_CONF_Format as SES_Confirmed_on,SES_CONF_Format, No_SES_to_today,Settlement_duration, (case ISDATE((case ISNULL(Invoiced_on,'') when '' then null ELSE ((case ISNULL(Invoiced_on,'') when '' then '' ELSE SUBSTRING(Invoiced_on,7,4) end)+'-'+(case ISNULL(Invoiced_on,'') when '' then '' ELSE SUBSTRING(Invoiced_on,4,2) end)+'-'+(case ISNULL(Invoiced_on,'') when '' then '' ELSE SUBSTRING(Invoiced_on,1,2) end)) end)) when 1 then (case ISNULL(Invoiced_on,'') WHEN '' then null else ((case ISNULL(Invoiced_on,'') when '' then '' ELSE SUBSTRING(Invoiced_on,7,4) end)+'-'+(case ISNULL(Invoiced_on,'') when '' then '' ELSE SUBSTRING(Invoiced_on,4,2) end)+'-'+(case ISNULL(Invoiced_on,'') when '' then '' ELSE SUBSTRING(Invoiced_on,1,2) end)) end) else null end) as Invoiced_on, Invoice_duration, (case ISDATE((case ISNULL(Payment_made_on,'') when '' then null ELSE ((case ISNULL(Payment_made_on,'') when '' then '' ELSE SUBSTRING(Payment_made_on,7,4) end)+'-'+(case ISNULL(Payment_made_on,'') when '' then '' ELSE SUBSTRING(Payment_made_on,4,2) end)+'-'+(case ISNULL(Payment_made_on,'') when '' then '' ELSE SUBSTRING(Payment_made_on,1,2) end)) end)) WHEN 1 then (case ISNULL(Payment_made_on,'') when '' then null ELSE ((case ISNULL(Payment_made_on,'') when '' then '' ELSE SUBSTRING(Payment_made_on,7,4) end)+'-'+(case ISNULL(Payment_made_on,'') when '' then '' ELSE SUBSTRING(Payment_made_on,4,2) end)+'-'+(case ISNULL(Payment_made_on,'') when '' then '' ELSE SUBSTRING(Payment_made_on,1,2) end)) end) else null end) as Payment_made_on, Payment_duration,DateIn,Remark1,Remark2,Accepted,Deleted,Blocked from CPT.dbo.FC_SESReport GO --==CPT==-- --CPTList truncate table FCLDB..CPTList insert into FCLDB..CPTList(CPT_No,Contract_No,CA,Contractor, FC_Desctription,FC_Price_Scheme,FC_Start_Date,FC_End_Date,Net_Amount,Tax_Amount, Tax,Currency,CT_Director,CT_GM,Comment,Report_Date,Remark,UserId,Last_Payment) select a.CPT_No,a.Contract_No,a.CA, (select top 1 c.ContractorId from Base_Contractor c where charindex(rtrim(ltrim(c.ContractorCN)), a.Contractor)>0 or charindex(rtrim(ltrim(c.Contractor)), a.Contractor)>0) as Contractor, a.FC_Desctription, p.PriceSchemeId as FC_Price_Scheme, (case ISDATE(a.FC_Start_Date) when 1 then a.FC_Start_Date else null end) as FC_Start_Date, (case ISDATE(a.FC_End_Date) when 1 then a.FC_End_Date else null end) as FC_Start_Date, (case ISNUMERIC(REPLACE(a.Net_Amount, ',', '')) when 1 then REPLACE(a.Net_Amount, ',', '') else null end) Net_Amount, (case ISNUMERIC(REPLACE(a.Tax_Amount, ',', '')) when 1 then REPLACE(a.Tax_Amount, ',', '') else null end) Tax_Amount, a.Tax,a.Currency, (select top 1 u.UserId from Sys_User u where charindex(rtrim(ltrim(u.ChineseName)), a.CT_Director)>0 or charindex(rtrim(ltrim(u.UserName)), a.CT_Director)>0) as CT_Director, a.CT_GM,Comment,a.Report_Date,a.Remark, (SELECT TOP 1 x.Contract_Admin FROM FCLDB..FC_SESRelatedData x WHERE x.FO_NO=a.Contract_No) AS UserId, 'Y' Last_Payment from CPT..CPTList as a --left join FCLDB..FC_SESRelatedData as b on a.Contract_No=b.FO_NO left join FCLDB..Base_PriceScheme as p on p.PriceScheme = a.FC_Price_Scheme GO --SESList truncate table FCLDB..SESList insert into FCLDB..SESList(SES,CPT_No,Requistioner,Short_Description,Start_Date,End_Date, Budget,Quotation,Net_Value,Tax_Value,Deviation,By_Perc,Deduction,Con_Days,BoQ_Days, SES_Days,Submit_Date,Remark,UserId) select a.SES,'CPT-'+(b.Contract_No)+SUBSTRING(a.CPT_No,10,LEN(a.CPT_No)) AS CPT_No, a.Requistioner,a.Short_Description, (case ISNULL(a.Start_Date,'') when '' then null ELSE ((case ISNULL(a.Start_Date,'') when '' then '' else SUBSTRING(a.Start_Date,7,4) end) +'-'+(case ISNULL(a.Start_Date,'') when '' then '' else SUBSTRING(a.Start_Date,4,2) end) +'-'+(case ISNULL(a.Start_Date,'') when '' then '' else SUBSTRING(a.Start_Date,1,2) end)) END) as Start_Date, (case ISNULL(a.End_Date,'') when '' then null else ((case ISNULL(a.End_Date,'') when '' then '' ELSE SUBSTRING(a.End_Date,7,4) end)+'-'+(case ISNULL(a.End_Date,'') when '' then '' ELSE SUBSTRING(a.End_Date,4,2) end)+'-'+(case ISNULL(a.End_Date,'') when '' then '' ELSE SUBSTRING(a.End_Date,1,2) end)) end) as End_Date, a.Budget,a.Quotation,a.Net_Value,a.Tax_Value,a.Deviation, (case when ISNUMERIC (By_Perc)>0 then CONVERT(DECIMAL(6,2),By_Perc)*100 ELSE NULL END) AS By_Perc, a.Deduction,a.Con_Days, a.BoQ_Days,a.SES_Days,a.Submit_Date,a.Remark,c.Contract_Admin UserId FROM CPT..SESList as a INNER join CPT..CPTList as b on a.CPT_No=b.CPT_No LEFT join FCLDB..FC_SESRelatedData as c on b.Contract_No=c.FO_NO GO --FC_SESReportToCPT(一定切记，FC_SESReport表里面的数据导入到系统里面后才可以执行，FC_SESReport表中数据是由SES report菜单导入进去的) --因为FC_SESReport表中没回导入的都是最新的，所以不需要去原表里面抓取数据，FC_SESReportToCPT表是新增的表，目的是为了区分SES和CPT的SES --切记，切记，切记，数据一定导入到系统后才可以执行此导入脚本，只可执行一次 insert into FCLDB..FC_SESReportToCPT(SES_No,Short_Descrption,Start_Date,End_Date,Created_by,Created_on, TECO_Date,TECO_Format,Requisitioner,FO,Item,Vendor_Name,Discipline,SSR_budget,Currency, Contractor_quotation,SSR_Actual_cost,Cost_checker,Tax_rate,Changed_by,Deviation, Deviation_Percentage,Overrun,Long_text,Work_Order,Function_location,Main_work_center, Work_Center,Dep,Section,Cost_center,WBS,Network,TODAY,Claim_sheets_receive,CS_REC_Format, No_SUBM_To_today,Contractor_duration,Engineer_confirmed_o,ENG_CONF_Format,No_BoQ_CONF_to_today, BoQ_confirmation_dur,SES_Confirmed_on,SES_CONF_Format,No_SES_to_today,Settlement_duration, Invoiced_on,Invoice_duration,Payment_made_on,Payment_duration,DateIn,Remark1,Remark2, Accepted,Deleted,Blocked,User_field,Reduced_by_quantity,UserId) select a.SES_No,a.Short_Descrption,a.Start_Date,a.End_Date,a.Created_by,a.Created_on, a.TECO_Date,a.TECO_Format,a.Requisitioner,a.FO,a.Item,a.Vendor_Name,a.Discipline,a.SSR_budget,a.Currency, a.Contractor_quotation,a.SSR_Actual_cost,a.Cost_checker,a.Tax_rate,a.Changed_by,a.Deviation, a.Deviation_Percentage,a.Overrun,a.Long_text,a.Work_Order,a.Function_location,a.Main_work_center, a.Work_Center,a.Dep,a.Section,a.Cost_center,a.WBS,a.Network,a.TODAY,a.Claim_sheets_receive,a.CS_REC_Format, a.No_SUBM_To_today,a.Contractor_duration,a.Engineer_confirmed_o,a.ENG_CONF_Format,a.No_BoQ_CONF_to_today, a.BoQ_confirmation_dur,a.SES_Confirmed_on,a.SES_CONF_Format,a.No_SES_to_today,a.Settlement_duration, a.Invoiced_on,a.Invoice_duration,a.Payment_made_on,a.Payment_duration,a.DateIn,a.Remark1,a.Remark2, a.Accepted,a.Deleted,a.Blocked,a.User_field,a.Reduced_by_quantity,d.Contract_Admin UserId FROM FCLDB..FC_SESReport as a inner join CPT..SESList as b on b.SES=a.SES_No left join CPT..CPTList as c on c.CPT_No=b.CPT_No left join FCLDB..FC_SESRelatedData as d on d.FO_NO=c.Contract_No GO --OEM UPDATE dbo.FC_SESRelatedData SET FOTypeId='58544360-5d26-4358-8459-928aa5f6e3ab' WHERE Type='8DA3CA5A-1BE2-4B8E-B794-3DCA8FEF9D6E' GO --TAR UPDATE dbo.FC_SESRelatedData SET FOTypeId='eb846026-639f-4063-8d3d-a253905ddfe0' WHERE FO_NO IN ('4955869592','4948283631','4948283628','4944996032','4944996029', '4955272920','4954367650','4954367649','4953576899','4953576900','4953576898', '4945360915','4945360916','4945360918','4949702138','4950678523','4950868006', '4950735378','4947530079','4952257851','4950735399','4954176265','4954367647', '4954367646','4954248437','4954248438','4954248439','4955057488','4955590479', '4954610379','4954610377','4954546470','4954546471','4954546472','4954967671', '4955305910','4956544261') GO --FC UPDATE dbo.FC_SESRelatedData SET FOTypeId='e3dd9ecc-4474-4e94-85ff-22dc073fd8b0' WHERE FOTypeId IS NULL GO UPDATE dbo.FC_SESRelatedData SET Original_WC= SUBSTRING(Original_WC,1,3) GO UPDATE dbo.FC_SESRelatedData SET DisciplineId= (SELECT TOP 1 DisciplineId FROM dbo.Base_Discipline d WHERE charindex(rtrim(ltrim(d.Discipline)), Contract_Title)>0) GO UPDATE dbo.FC_SESRelatedData SET Contractor='335C267D-24E7-4125-8FE7-6A0E8F05BC58' WHERE FO_NO='A546061365' GO --删除空SES_No为空的记录 DELETE FROM dbo.SSR WHERE (SES_No IS NULL OR SES_No='') GO UPDATE dbo.CPTList SET Contractor =(SELECT TOP 1 f.Contractor FROM dbo.FC_SESRelatedData f WHERE f.FO_NO=CPTList.Contract_No ) WHERE CPTList.Contractor IS NULL GO UPDATE dbo.CPTList SET CPT_No='CPT-'+Contract_No+SUBSTRING(CPT_No,10,LEN(CPT_No)) GO UPDATE dbo.SESList SET CPT_No='CPT-'+(SELECT TOP 1 fo FROM dbo.FC_SESReportToCPT c WHERE c.SES_No=SES)+SUBSTRING(CPT_No,10,LEN(CPT_No)) GO