title: SQL Server Merge, how to subset target by date range (year)
body: <p>I have been reading up on the MERGE syntax in SQL Server and it's perfect for what I need it to do, however I cannot for the life of me figure out how to prevent the target table from deleting old data that I don't want to match against.</p>
  
  <p>I have 11 million rows in Target, and I only want to match current year for changes which is around ~300k rows. Obviously will be massive performance difference.</p>
  
  <p>My code:</p>
  
  <pre><code>    MERGE  [dbo].[RE_Gifts_Backup_testing] as Target   --[TARGET is main table]
      USING  [dbo].[RE_Gifts_CY_Changes] as Source  --[SOURCE is data with new changes]
  
      ON (Target.gift_id = Source.gift_id)  -- What are we matching rows on.
  
  
  
  
      --When rows are matched, update the records if there is any change
  
      WHEN MATCHED
              AND year(Target.gift_date) = '2016'
              AND year(Source.Gift_Date) = '2016' 
              AND (TARGET.[Constituent_ID] &lt;&gt; SOURCE.[Constituent_ID]
              OR  TARGET.[RE_Gift_ID] &lt;&gt; SOURCE.[RE_Gift_ID] 
              OR  TARGET.[Gift_ID] &lt;&gt; SOURCE.[Gift_ID] 
              OR  TARGET.[Gift_Date_Added] &lt;&gt; SOURCE.[Gift_Date_Added] 
              OR  TARGET.[Gift_Date] &lt;&gt; SOURCE.[Gift_Date] 
              OR  TARGET.[Name] &lt;&gt; SOURCE.[Name] 
              OR  TARGET.[Gift_Type] &lt;&gt; SOURCE.[Gift_Type] 
              OR  TARGET.[Gift_Amount] &lt;&gt; SOURCE.[Gift_Amount] 
              OR  TARGET.[Frequency] &lt;&gt; SOURCE.[Frequency] 
              OR  TARGET.[Pay_Method] &lt;&gt; SOURCE.[Pay_Method]
              OR  TARGET.[Appeal] &lt;&gt; SOURCE.[Appeal]
              OR  TARGET.[Campaign] &lt;&gt; SOURCE.[Campaign]
              OR  TARGET.[Gift Added By] &lt;&gt; SOURCE.[Gift Added By]
              OR  TARGET.[Gift Reference] &lt;&gt; SOURCE.[Gift Reference]
              OR  TARGET.[SoftCredit] &lt;&gt; SOURCE.[SoftCredit]
              OR  TARGET.[Relationship Manager] &lt;&gt; SOURCE.[Relationship Manager]
              OR  TARGET.[CostCentre] &lt;&gt; SOURCE.[CostCentre] 
              OR  TARGET.[Recruiter] &lt;&gt; SOURCE.[Recruiter] 
              OR  TARGET.[Sitecode] &lt;&gt; SOURCE.[Sitecode] 
              OR  TARGET.[Zerodebit] &lt;&gt; SOURCE.[Zerodebit] 
              OR  TARGET.[Donation_Channel] &lt;&gt; SOURCE.[Donation_Channel] 
              OR  TARGET.[Source_Channel] &lt;&gt; SOURCE.[Source_Channel] 
              OR  TARGET.[Recruitment_Source] &lt;&gt; SOURCE.[Recruitment_Source] 
              OR  TARGET.[Merch_ProductID] &lt;&gt; SOURCE.[Merch_ProductID] )
  
  
          THEN UPDATE 
              set 
                TARGET.[Constituent_ID] = SOURCE.[Constituent_ID],
                TARGET.[RE_Gift_ID] = SOURCE.[RE_Gift_ID],
                TARGET.[Gift_ID] = SOURCE.[Gift_ID], 
                TARGET.[Gift_Date_Added] = SOURCE.[Gift_Date_Added], 
                TARGET.[Gift_Date] = SOURCE.[Gift_Date], 
                TARGET.[Name] = SOURCE.[Name],
                TARGET.[Gift_Type] = SOURCE.[Gift_Type],
                TARGET.[Gift_Amount] = SOURCE.[Gift_Amount],
                TARGET.[Frequency] = SOURCE.[Frequency],
                TARGET.[Pay_Method] = SOURCE.[Pay_Method],
                TARGET.[Appeal] = SOURCE.[Appeal],
                TARGET.[Campaign] = SOURCE.[Campaign],
                TARGET.[Gift Added By] = SOURCE.[Gift Added By],
                TARGET.[Gift Reference] = SOURCE.[Gift Reference],
                TARGET.[SoftCredit] = SOURCE.[SoftCredit],
                TARGET.[Relationship Manager] = SOURCE.[Relationship Manager],
                TARGET.[CostCentre] = SOURCE.[CostCentre],
                TARGET.[Recruiter] = SOURCE.[Recruiter],
                TARGET.[Sitecode] = SOURCE.[Sitecode],
                TARGET.[Zerodebit] = SOURCE.[Zerodebit],
                TARGET.[Donation_Channel] = SOURCE.[Donation_Channel],
                TARGET.[Source_Channel] = SOURCE.[Source_Channel],
                TARGET.[Recruitment_Source] = SOURCE.[Recruitment_Source],
                TARGET.[Merch_ProductID] = SOURCE.[Merch_ProductID]
  
      -- when no records are matched then insert from source into target.
      WHEN NOT MATCHED BY TARGET and year(target.gift_date) &gt;= '2016' THEN 
  
      INSERT ([Constituent_ID],[RE_Gift_ID],[Gift_ID],[Gift_Date_Added],[Gift_Date],[Name],[Gift_Type],[Gift_Amount],[Frequency],[Pay_Method],[Appeal],[Campaign],[Gift Added By],[Gift Reference],
                  [SoftCredit],[Relationship Manager],[CostCentre],[Recruiter],[Sitecode],[Zerodebit],[Donation_Channel],[Source_Channel],[Recruitment_Source],[Merch_ProductID])
  
      VALUES (source.[Constituent_ID],source.[RE_Gift_ID],source.[Gift_ID],source.[Gift_Date_Added],source.[Gift_Date],source.[Name],source.[Gift_Type],source.[Gift_Amount],source.[Frequency],
                  source.[Pay_Method],source.[Appeal],source.[Campaign],source.[Gift Added By],source.[Gift Reference],source.[SoftCredit],source.[Relationship Manager],source.[CostCentre],
                  source.[Recruiter],source.[Sitecode],source.[Zerodebit],source.[Donation_Channel],source.[Source_Channel],source.[Recruitment_Source],source.[Merch_ProductID])
  
      --When there is a row that exists in target table and same record does not exist in source table then delete this record from target table
      WHEN NOT MATCHED BY SOURCE and year(target.gift_date) &gt;= '2016' THEN 
      DELETE
  
      OUTPUT $action, 
              deleted.[Constituent_ID] as [deletedConstituent_ID],
              deleted.[RE_Gift_ID] as [deletedRE_Gift_ID],
              deleted.[Gift_ID] as [deletedGift_ID], 
              deleted.[Gift_Date_Added] as [deletedGift_Date_Added], 
              deleted.[Gift_Date] as [deletedGift_Date], 
              deleted.[Name] as [deletedName],
              deleted.[Gift_Type] as [deletedGift_Type],
              deleted.[Gift_Amount] as [deletedGift_Amount],
              deleted.[Frequency] as [deletedFrequency],
              deleted.[Pay_Method] as [deletedPay_Method],
              deleted.[Appeal] as [deletedAppeal],
              deleted.[Campaign] as [deletedCampaign],
              deleted.[Gift Added By] as [deletedGift Added By],
              deleted.[Gift Reference] as [deletedGift Reference],
              deleted.[SoftCredit] as [deletedSoftCredit],
              deleted.[Relationship Manager] as [deletedRelationship Manager],
              deleted.[CostCentre] as [deletedCostCentre],
              deleted.[Recruiter] as [deletedRecruiter],
              deleted.[Sitecode] as [deletedSitecode],
              deleted.[Zerodebit] as [deletedZerodebit],
              deleted.[Donation_Channel] as [deletedDonation_Channel],
              deleted.[Source_Channel] as [deletedSource_Channel],
              deleted.[Recruitment_Source] as [deletedRecruitment_Source],
              deleted.[Merch_ProductID] as [deletedMerch_ProductID],
  
              inserted.[Constituent_ID] as [insertedConstituent_ID],
              inserted.[RE_Gift_ID] as [insertedRE_Gift_ID],
              inserted.[Gift_ID] as [insertedGift_ID], 
              inserted.[Gift_Date_Added] as [insertedGift_Date_Added], 
              inserted.[Gift_Date] as [insertedGift_Date], 
              inserted.[Name] as [insertedName],
              inserted.[Gift_Type] as [insertedGift_Type],
              inserted.[Gift_Amount] as [insertedGift_Amount],
              inserted.[Frequency] as [insertedFrequency],
              inserted.[Pay_Method] as [insertedPay_Method],
              inserted.[Appeal] as [insertedAppeal],
              inserted.[Campaign] as [insertedCampaign],
              inserted.[Gift Added By] as [insertedGift Added By],
              inserted.[Gift Reference] as [insertedGift Reference],
              inserted.[SoftCredit] as [insertedSoftCredit],
              inserted.[Relationship Manager] as [insertedRelationship Manager],
              inserted.[CostCentre] as [insertedCostCentre],
              inserted.[Recruiter] as [insertedRecruiter],
              inserted.[Sitecode] as [insertedSitecode],
              inserted.[Zerodebit] as [insertedZerodebit],
              inserted.[Donation_Channel] as [insertedDonation_Channel],
              inserted.[Source_Channel] as [insertedSource_Channel],
              inserted.[Recruitment_Source] as [insertedRecruitment_Source],
              inserted.[Merch_ProductID] as [insertedMerch_ProductID],
  
              updated.[Constituent_ID] as [updatedConstituent_ID],
              updated.[RE_Gift_ID] as [updatedRE_Gift_ID],
              updated.[Gift_ID] as [updatedGift_ID], 
              updated.[Gift_Date_Added] as [updatedGift_Date_Added], 
              updated.[Gift_Date] as [updatedGift_Date], 
              updated.[Name] as [updatedName],
              updated.[Gift_Type] as [updatedGift_Type],
              updated.[Gift_Amount] as [updatedGift_Amount],
              updated.[Frequency] as [updatedFrequency],
              updated.[Pay_Method] as [updatedPay_Method],
              updated.[Appeal] as [updatedAppeal],
              updated.[Campaign] as [updatedCampaign],
              updated.[Gift Added By] as [updatedGift Added By],
              updated.[Gift Reference] as [updatedGift Reference],
              updated.[SoftCredit] as [updatedSoftCredit],
              updated.[Relationship Manager] as [updatedRelationship Manager],
              updated.[CostCentre] as [updatedCostCentre],
              updated.[Recruiter] as [updatedRecruiter],
              updated.[Sitecode] as [updatedSitecode],
              updated.[Zerodebit] as [updatedZerodebit],
              updated.[Donation_Channel] as [updatedDonation_Channel],
              updated.[Source_Channel] as [updatedSource_Channel],
              updated.[Recruitment_Source] as [updatedRecruitment_Source],
              updated.[Merch_ProductID] as [updatedMerch_ProductID];
  
  
  
      SELECT @@ROWCOUNT;
      GO
  </code></pre>
  
  <p>this throws an error:</p>
  
  <blockquote>
    <p>Msg 5334, Level 16, State 2, Line 86<br>
    The identifier 'target.gift_date' cannot be bound. Only target columns and columns in the clause scope are allowed in the 'WHEN NOT MATCHED BY SOURCE' clause of a MERGE statement.</p>
  </blockquote>