新旧数据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