检查数据：存在就更新 不存在就新增

USE LoadData
go
  UPDATE dbo.XimaLss SET BParent_fullname =c.BParent_fullname  FROM    
       ( SELECT  a.JxsID,a.Btypeid,a.AreaID,a.BParent_fullname FROM dbo.XimaBill a INNER JOIN 
        (SELECT jxsid,Btypeid,MAX(Savedate) AS savedate FROM dbo.XimaBill WHERE VchType=11 GROUP BY jxsid,Btypeid ) b
        ON a.Btypeid =b.Btypeid AND a.JxsID = b.jxsid AND a.Savedate =b.savedate 
          ) c
      where  XimaLss.JxsID =c.JxsID  AND  XimaLss.Btypeid=c.Btypeid AND 
XimaLss.AreaID=  c.AreaID AND ISNULL( XimaLss.BParent_fullname,'') != ISNULL(c.BParent_fullname,'') ;
      
INSERT INTO ximalss  (
        JxsName ,
        JxsID ,
        BParent_fullname ,
        Btypeid ,
        Bfullname ,
        Bname ,
        Busercode ,       
        AreaID,CreateTime  )  
        SELECT    DISTINCT  
       XimaBill.JxsName ,
       XimaBill. JxsID ,
       XimaBill. BParent_fullname , 
       XimaBill.  Btypeid ,
       XimaBill.  Bfullname ,
       XimaBill.  Bname ,
       XimaBill.  Busercode ,    
       XimaBill. AreaID, 
        GETDATE() AS CreateTime  FROM dbo.XimaBill  INNER  JOIN  dbo.XimaArea ON dbo.XimaBill.AreaID = dbo.XimaArea.AreaID
        WHERE VchType=11   AND 
         NOT  EXISTS (SELECT   1  FROM  dbo.XimaLss a  WHERE a.JxsID =XimaBill.JxsID  AND  a.Btypeid=XimaBill.Btypeid AND a.AreaID=  dbo.XimaBill.AreaID );
go

 

转载于:https://www.cnblogs.com/zhaoyl9/p/11096906.html