插入数据时，如果原表没有对应的数据，则insert，如果有对应的数据则

-更新前的数据量统计
SELECT  COUNT(1)
FROM    PL_PM_ApportionInvestment 

 

--插入不存在的新数据
INSERT  INTO dbo.PL_PM_ApportionInvestment
        ( ProjectID ,
          CompanyCode ,
          BuildCompany ,
          CitySupporting ,
          CityInvestment ,
          [Year] ,
          Source ,
          IsTarget
        )
        SELECT  ProjectID ,
                CompanyCode ,
                BuildCompany ,
                CitySupporting ,
                CityInverstment ,
                '20' + SUBSTRING(A.MISNUMBER, 2, 2) [YEAR] ,
                Source ,
                IsTarget
        FROM    dbo.投资数据_1130 A
                INNER JOIN dbo.PL_PM_Project B ON A.MISNUMBER = B.MISNUMBER
        WHERE   NOT EXISTS ( SELECT 1
                             FROM   dbo.PL_PM_ApportionInvestment c
                             WHERE  b.ProjectID = c.ProjectID
                                    AND a.CompanyCode = c.CompanyCode
                                    AND a.Source = c.Source
                                    AND c.Year = '20' + SUBSTRING(A.MISNUMBER,
                                                              2, 2) )
 
 
--更新存在的数据
UPDATE  dbo.PL_PM_ApportionInvestment
SET     BuildCompany = d.BuildCompany ,
        CitySupporting = d.CitySupporting ,
        CityInvestment = d.CityInverstment
FROM    ( SELECT    ProjectID ,
                    CompanyCode ,
                    BuildCompany ,
                    CitySupporting ,
                    CityInverstment ,
                    '20' + SUBSTRING(A.MISNUMBER, 2, 2) [YEAR] ,
                    Source ,
                    IsTarget
          FROM      dbo.投资数据_1130 A
                    INNER JOIN dbo.PL_PM_Project B ON A.MISNUMBER = B.MISNUMBER
          WHERE     EXISTS ( SELECT 1
                             FROM   dbo.PL_PM_ApportionInvestment c
                             WHERE  b.ProjectID = c.ProjectID
                                    AND a.CompanyCode = c.CompanyCode
                                    AND a.Source = c.Source
                                    AND c.Year = '20' + SUBSTRING(A.MISNUMBER,
                                                              2, 2) )
        ) d
WHERE   PL_PM_ApportionInvestment.ProjectID = d.ProjectID
        AND PL_PM_ApportionInvestment.CompanyCode = d.CompanyCode
        AND PL_PM_ApportionInvestment.Source = d.SOURCE
        AND PL_PM_ApportionInvestment.Year = d.year

 

转载于:https://www.cnblogs.com/hgblog/archive/2012/12/24/2831255.html