title: Using multiple match criteria in SQL server merge
body: <p>At this point of time Source Table and destination Table is having Three columns 'ID', 'ST' and 'ADD'. 'ADD' allows null</p>
  
  <p>Using <strong><em>SQL Server merge</em></strong> statement in I want to put changes made in source Table to destination.
  Requirement:</p>
  
  <ol>
  <li>If <strong><em>ID</em></strong> is <strong>available in source but not in target</strong> then <strong>insert</strong> a new
  row in target</li>
  <li>For any <strong><em>ID</em> available in both source and target</strong>, if <strong>only <em>ADD</em></strong> is
  <strong>changes</strong> in source then <strong>insert</strong> new row in target. i.e If <em>ADD</em> for
  <em>ID=2</em> changed twice, then we will be having three rows(original + two changes) for <em>ID=2</em> in target, as we are inserting new row every
  time</li>
  <li>For any <strong><em>ID</em> available in both source and target</strong>, if <strong>only <em>ST</em>
  changes</strong> in source then modified value of <em>ST</em> should be <strong>updated</strong> in
  all rows for matched <em>ID</em> in target</li>
  <li>For <strong>any <em>ID</em> available in both source and target</strong>, if <strong>both <em>ST</em> and
  <em>ADD</em> changes</strong> in source, then new row should be <strong>inserted</strong> for new <em>ADD</em> and all old rows should updated for that <em>ID</em> with <strong>updated</strong> value of <em>ST</em></li>
  </ol>
  
  <p>Can I achieve this with SQL Server merge? If not, what are the alternatives we have?</p>