title: SQL insert statement with select
body: <p>I have the following SQL statement</p>
  
  <pre><code>INSERT INTO A_Table (field1, field2) 
    SELECT field1, field2 FROM B_Table
    WHERE field1 NOT IN (
      SELECT field1 FROM A_Table);
  </code></pre>
  
  <p>basically, I need to copy all the data from B_Table to A_Table, with a constrain: field1 from B_Table not existing in A_Table. I need to add this constrain since field1 is a key.</p>
  
  <p>It looks like a conflict: field1 not in A_Table being copied from B_Table to A_Table. During the inserting process, the copied record from B_Table will exist in A_Table. Not sure if it does work or not, or any unexpected result?</p>
  
  <p>Maybe it is OK. The last SELECT is cached in a temporary table, and not refreshed during inserting?</p>
  
  <p>By the way, my SQL server is Microsoft SQL server 2005.</p>