新舊資料ICD的整合

--2. 建立Upate_Ref_Education_ICD_ByEduName儲存過程，根據EduName和新的ICD串增量更新表 if Exists(select name from sysobjects where NAME = 'Upate_Ref_Education_ICD_ByEduName' and type='P') drop procedure Upate_Ref_Education_ICD_ByEduName GO CREATE PROC Upate_Ref_Education_ICD_ByEduName @EduName VARCHAR(1000),@newICD VARCHAR(MAX) AS --功能: @newICD+@oldICD==>@mergeICD (並更新Ref_Education中對應的ICD)， 但@oldICD中已有的資料不得再新增 DECLARE @oldICD VARCHAR(MAX),@mergeICD VARCHAR(MAX) --舊資料， 新資料 (去除空格與換行等) SELECT @oldICD=CONVERT(VARCHAR(max),ICD) FROM Ref_Education WHERE EduName =@EduName SET @oldICD =REPLACE(REPLACE(REPLACE(REPLACE(@oldICD, CHAR(13), ''), CHAR(10), ''),CHAR(9),''),' ','') SET @newICD =REPLACE(REPLACE(REPLACE(REPLACE(@newICD, CHAR(13), ''), CHAR(10), ''),CHAR(9),''),' ','') --將表中的資料形成一串 Select @mergeICD= stuff((select ','+id from ( SELECT id FROM fn_Split(@oldICD,',') UNION ALL SELECT id FROM fn_Split(@newICD,',') WHERE id NOT IN( SELECT id FROM fn_Split(@oldICD,',')) ) AS T for xml path('')),1,1,'') PRINT '----------'+@EduName+'----------' PRINT 'OLD ICD:'+@oldICD PRINT 'NEW ICD:'+@newICD PRINT 'MERGE ICD:'+@mergeICD + CHAR(13) --更新資料 UPDATE Ref_Education SET ICD = @mergeICD WHERE EduName=@EduName GO