title: what's the best way to filter target table using merge statment
body: <p>I need to merge two tables in SQL Server 2008 filtering the source and the target by productid field, let say I've a source table @source (productid,userid) and @target with the same structure, I need to merge the two tables but only for the records having a specified productid. </p>
  
  <p>The sample above it's OK just I need to know if there is a better way to delete only the record with <code>productId = @productId</code> not matching the source compared to adding <code>and TARGET.productid = @productid</code> operator in
  NOT MATCHED BY SOURCE?</p>
  
  <pre><code>declare @productid int = 1
  declare @source table (productid int, userid int)
  declare @target table (productid int, userid int)
  insert into @source (productid , userid ) values(1,1)
  insert into @source (productid , userid ) values(1,2)
  insert into @source (productid , userid ) values(1,3)
  insert into @source (productid , userid ) values(2,1)
  insert into @source (productid , userid ) values(2,2)
  insert into @source (productid , userid ) values(2,3)
  
  insert into @target (productid , userid ) values(1,4)
  insert into @target (productid , userid ) values(2,5)
  insert into @target (productid , userid ) values(3,1)
  insert into @target (productid , userid ) values(4,1);
  
  WITH n (productid , userid )
  AS
  (
  select productid , userid from @source where productid = @productid
  )
  MERGE @target AS TARGET 
  USING n  AS SOURCE 
  ON (TARGET.productid = SOURCE.productid and TARGET.userid = SOURCE.userid) 
  WHEN NOT MATCHED BY TARGET THEN 
  INSERT (productid , userid) 
  VALUES (SOURCE.productid, SOURCE.userid)
  WHEN NOT MATCHED BY SOURCE and TARGET.productid  = @productid  THEN 
  DELETE;
  
  select * from @target order by 1,2
  </code></pre>