title: SQL Server Primary key violation due to truncate/delete?
body: <p>I've got a set of staging tables where I accept data, scrub, and cleanse them before inserting to the target table.  The target table has a primary key constraint and what I'm inserting is a primary key. </p>
  
  <p>I check for the absence of the primary key in the target table before I insert.  I insert only records that ARE NOT in the target table, based on the primary key's absence:</p>
  
  <pre><code>INSERT INTO Target
      SELECT 
          primKey
          , user_state
          , test_state
      FROM
          myStagingTable3
  </code></pre>
  
  <p>By this point, the stagingTable3 has only data that are NOT present in the Target Table using the following where clause:</p>
  
  <pre><code>WHERE
          primKey not in (Select primKey from Target)
  </code></pre>
  
  <p>Somehow, I'm getting a primary key violation error :</p>
  
  <blockquote>
    <p>Msg 2627, Level 14, State 1.  (procedure failed at line #...)
    Violation of Primary Key constraint 'pk1101AE.'  Cannot insert
    duplicate key in object 'Target'</p>
  </blockquote>
  
  <p>My questions:</p>
  
  <ul>
  <li>under what conditions could a primary key violation occur when the
  key that I want to insert is NOT present in the target table? </li>
  <li>could a prior delete of records cause the primary key to be retained?  If so,
  can I work around this somehow?</li>
  <li>something else?</li>
  </ul>
  
  <p>It's pretty clear that my staging tables have the key and that the Target table does not.  Yet the insert fails.</p>