title: CTE with insert and update in sql server 2008
body: <p>I am using SQL Server 2008 and I want to know if I can use CTE with insert and update together e.g. if one condition fails then insert else update.</p>
  
  <pre><code>With X As
  (
      Select COMP 
      From comp 
  )
  IF EXISTS (  Select * From  A Where A.COMP = X.COMP )
  
  Insert Into A (COMP,comp1)
      select X.COMP, @COMP1 from X
  Else
      Update A set comp1=@comp1 
      where comp= X.Comp
  </code></pre>
  
  <p>I have tried to use</p>
  
  <pre><code> With X As
      (
          Select COMP 
          From comp 
      )
  
      Insert Into A (COMP,comp1)
          select X.COMP, @COMP1 from X
   With Y As
      (
          Select COMP 
          From comp 
      )
    Update A set comp1=@comp1 
    join Y on comp= Y.Comp
  </code></pre>
  
  <p>But if it is a insert then it is inserting the record and also it is executing the Update block . Can you tell me how to avoid that with If statement.</p>