title: Why is the target table of a MERGE statement not allowed to have enabled rules?
body: <p>We have a process updating the database which uses the following SQL</p>
  
  <pre><code>IF NOT EXISTS (SELECT * FROM Target_Table WHERE Target_Table_ID = ...)
  BEGIN
      INSERT ...
  END
  ELSE
  BEGIN
      UPDATE ...
  END
  </code></pre>
  
  <p>I wanted to change this to use a <code>MERGE</code> statement in order to avoid a possible race condition where the <code>IF</code> and then <code>INSERT</code> or <code>UPDATE</code> statements are evaluated separately.</p>
  
  <p>However, as soon as I executed the query I got the error:</p>
  
  <blockquote>
    <p>The target table 'Target_Table' of the MERGE statement cannot have any enabled rules.  Found rule 'TargetTable_RULE'</p>
  </blockquote>
  
  <p>I understand the documentation states that the table cannot have rules enabled, although I missed it when first reading it, and apart from disabling the rule before performing the <code>MERGE</code> (which I don't see as a workable solution) it seems I'm stuck with the <code>IF NOT EXISTS</code> solution.</p>
  
  <p>What the document does not explain is <em>why</em> the target table is not allowed to have rules. In my case the rule is a simple <code>&gt; 0</code> check on a value.</p>
  
  <p>Does anyone know why rules are not allowed in this situation and if there is another way to perform the upsert in an atomic way?</p>
  
  <p><strong>EDIT:</strong> As Andriy M commented the question <a href="https://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005">Atomic UPSERT in SQL Server 2005</a> already has a wealth of information on atomic upserts so I'll not duplicate the discussion here. Instead I'd like just to know why the <code>MERGE</code> statement cannot execute on tables with rules defined and is there a work-around?</p>