title: Update and Insert When Condition is Matched in TSQL-Merge
body: <p>I have been trying to Write a Stored Procedure where i can perform UpSert using Merge with the Following Condition</p>
  
  <ol>
  <li><p>If Record is Present then change EndDate of Target to Yesterday's day i.e., Present Day - 1</p></li>
  <li><p>If Record is not Present then Insert New Record</p></li>
  </ol>
  
  <p>Here is the Table tblEmployee i used in SP</p>
  
  <pre><code>CREATE TABLE tblEmployee
  (
      [EmployeeID] [int] IDENTITY(1,1) NOT NULL, 
      [Name] [varchar](10) NOT NULL,  
      [StartDate] [date] NOT NULL,
      [EndDate] [date] NOT NULL
  )
  </code></pre>
  
  <p>Here is my SP which Takes UDTT as Input parameter</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[usp_UpsertEmployees]
  @typeEmployee typeEmployee READONLY -- It has same column like tblEmployye except EmployeeID
  AS
  BEGIN
      SET NOCOUNT ON;      
  
      MERGE INTO tblEmployee AS TARGET
      USING @typeEmployee AS SOURCE
      ON TARGET.Name = SOURCE.Name 
  
      WHEN MATCHED and TARGET.StartDate &lt; SOURCE.StartDate
      THEN 
  
              --First Update Existing Record EndDate to Previous Date as shown below 
              UPDATE 
              set TARGET.EndDate = DATEADD(day, -1, convert(date, SOURCE.StartDate))
  
              -- Now Insert New Record 
              --INSERT VALUES(SOURCE.Name, SOURCE.StartDate, SOURCE.EndDate);
  
      WHEN NOT MATCHED by TARGET 
      THEN
              INSERT VALUES(SOURCE.Name, SOURCE.StartDate, SOURCE.EndDate);
  
      SET NOCOUNT OFF;        
  END
  </code></pre>
  
  <p>How can i perform both Updating Existing Record and Adding New Record When Column is matched </p>
  
  <p>Can Please someone Explain me the Execution Flow of Merge in TSQL i.e., </p>
  
  <pre><code>    WHEN MATCHED --Will this Execute Everytime
  
      WHEN NOT MATCHED by TARGET -- Will this Execute Everytime
  
      WHEN NOT MATCHED by SOURCE -- Will this Execute Everytime
  </code></pre>
  
  <p>Will all above 3 condition get executed everytime in Merge or only Matching condition is executed Everytime</p>
  
  <p>Thanks in Advance</p>