SQL SERVER开发失败案例

 该程序存在两个严重问题：

1、取增量时没有先关联出主键，导致不需要更新的数据也跟着更新，浪费资源；

2、条件EXISTS(SELECT 1 FROM TMP WHERE SgnMonth=A.SgnMonth AND AreaCode=A.AreaCode)放错位置，导致更新主源表任意一条数据，都会将目标表其他历史数据置为无效数据，此条件应该放在where之后

  WITH TMP AS 
    ( 
    SELECT SgnMonth,AreaCode 
    FROM DCSYS.INDU_AREA_ESTATE_SALE A   
    WHERE UPDATETIME>TO_TIMESTAMP('''''+ CONVERT(NVARCHAR(27),@NOWTIME,21)+''''',''''YYYY-MM-DD HH24:MI:SS.FF'''')     
    AND UPDATETIME<=TO_TIMESTAMP('''''+ CONVERT(NVARCHAR(27),@MAXTIME,21)+''''',''''YYYY-MM-DD HH24:MI:SS.FF'''')   
    OR EXISTS(SELECT 1 FROM DCSYS.MAC_Parameter_Area   
              WHERE UPDATETIME>TO_TIMESTAMP('''''+ CONVERT(NVARCHAR(27),@NOWTIME,21)+''''',''''YYYY-MM-DD HH24:MI:SS.FF'''')     
              AND UPDATETIME<=TO_TIMESTAMP('''''+ CONVERT(NVARCHAR(27),@MAXTIME,21)+''''',''''YYYY-MM-DD HH24:MI:SS.FF'''')   
              AND AreaCode=A.AreaCode   
              )     
    ),   
    TMP1 AS                       
   (                     
   SELECT                
      A.SgnMonth, 
      A.AreaCode, 
      B.AreaName, 
      A.SaleArea, 
      A.ReadyHouseArea, 
      A.ForwardHouseArea, 
      A.ResidentArea, 
      A.ResidentReadyArea, 
      A.ResidentForwardArea, 
      A.OfficeArea, 
      A.OfficeReadyArea, 
      A.OfficeForwardArea, 
      A.BusinessArea, 
      A.BusinessReadyArea, 
      A.BusinessForwardArea, 
      A.SaleValue, 
      A.ResidentValue, 
      A.OfficeValue, 
      A.BusinessValue, 
      A.ForSaleArea, 
      A.ResidentForSaleArea, 
      A.OfficeForSaleArea, 
      A.BusinessForSaleArea, 
      A.SaleAreaYoY, 
      A.ReadyHouseAreaYoY, 
      A.ForwardHouseAreaYoY, 
      A.ResidentAreaYoY, 
      A.ResidentReadyAreaYoY, 
      A.ResidentForwardAreaYoY, 
      A.OfficeAreaYoY, 
      A.OfficeReadyAreaYoY, 
      A.OfficeForwardAreaYoY, 
      A.BusinessAreaYoY, 
      A.BusinessReadyAreaYoY, 
      A.BusinessForwardAreaYoY,     
      A.SaleValueYoY,                       
      A.ResidentValueYoY,     
      A.OfficeValueYoY,     
      A.BusinessValueYoY,     
      A.ForSaleAreaYoY,                      
      A.ResidentForSaleAreaYoY,     
      A.OfficeForSaleAreaYoY,     
      A.BusinessForSaleAreaYoY,     
      A.UPDATEID,                      
      A.UPDATETIME,            
      A.UPDATESTATE,                       
      A.BUSINESSTIME                     
   FROM DCSYS.INDU_AREA_ESTATE_SALE A   
   LEFT JOIN DCSYS.MAC_Parameter_Area B                       
   ON A.AreaCode=B.AreaCode AND B.UPDATESTATE<=1    
   AND EXISTS(SELECT 1 FROM TMP WHERE SgnMonth=A.SgnMonth AND AreaCode=A.AreaCode)   
   )                       
   SELECT                     
      SgnMonth, 
      AreaCode, 
      AreaName, 
      SaleArea, 
      ReadyHouseArea, 
      ForwardHouseArea, 
      ResidentArea, 
      ResidentReadyArea, 
      ResidentForwardArea, 
      OfficeArea, 
      OfficeReadyArea, 
      OfficeForwardArea, 
      BusinessArea, 
      BusinessReadyArea, 
      BusinessForwardArea, 
      SaleValue, 
      ResidentValue, 
      OfficeValue, 
      BusinessValue, 
      ForSaleArea, 
      ResidentForSaleArea, 
      OfficeForSaleArea, 
      BusinessForSaleArea, 
      SaleAreaYoY,     
      ReadyHouseAreaYoY,     
      ForwardHouseAreaYoY,     
      ResidentAreaYoY,     
      ResidentReadyAreaYoY,     
      ResidentForwardAreaYoY,  
      OfficeAreaYoY, 
      OfficeReadyAreaYoY,     
      OfficeForwardAreaYoY,     
      BusinessAreaYoY,     
      BusinessReadyAreaYoY,     
      BusinessForwardAreaYoY,     
      SaleValueYoY,     
      ResidentValueYoY,     
      OfficeValueYoY,     
      BusinessValueYoY,     
      ForSaleAreaYoY,     
      ResidentForSaleAreaYoY,     
      OfficeForSaleAreaYoY,     
      BusinessForSaleAreaYoY,     
      UPDATEID,     
      UPDATETIME,     
      CASE WHEN AreaName IS NULL THEN 2 ELSE UPDATESTATE END AS UPDATESTATE,     
      BUSINESSTIME     
   FROM TMP1