SQL insert statement with select

I have the following SQL statement

INSERT INTO A_Table (field1, field2) 
  SELECT field1, field2 FROM B_Table
  WHERE field1 NOT IN (
    SELECT field1 FROM A_Table);

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.

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?

Maybe it is OK. The last SELECT is cached in a temporary table, and not refreshed during inserting?

By the way, my SQL server is Microsoft SQL server 2005.

From stackoverflow

• Well it looks like that query wouldn't work in its current form because you aren't specifying a column in your sub query. Most likely it is still executing and it is assuming you mean not in (null)

• SQL statements are executed as if everything happens instantly. There's no point in time when (logically speaking) the instruction has been partially executed.

• The result set of the SELECT is determined before any inserting takes place, so there is no conflict. Otherwise, such statements would never be safe. It's a bit like the code:

  x = 1;
  x = x + x + 1;
  

  The right hand side is evaluated, and only then assigned to the variable on the left hand side - otherwise who knows how x would end up!

• It should work just fine.

  You should always have a test environment set up where you can try stuff to see what happens. You never want to rely on the advice of strangers before you mess up your database!

• maybe it will help if you look at you query as on two separate statements

  the select statement

    SELECT field1, field2 FROM B_Table
    WHERE field1 NOT IN (
      SELECT field1 FROM A_Table)
  

  which gives you some results

  now you insert those results into your table A_table

  INSERT INTO A_Table (field1, field2)
  

  and the code to accomplish this is

  INSERT INTO A_Table (field1, field2)
    SELECT field1, field2 FROM B_Table
    WHERE field1 NOT IN (
      SELECT field1 FROM A_Table)
  

  which is basically what you have posted

• your insert statement will work as demonstrated by this SQL -

  CREATE TABLE #temp1 (id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , [name] NVARCHAR(20))
  CREATE TABLE #temp2 (id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED , [name] NVARCHAR(20))
  
  INSERT INTO #temp1 ([name])
  VALUES ('name1')
  INSERT INTO #temp1 ([name])
  VALUES ('name2')
  INSERT INTO #temp1 ([name])
  VALUES ('name3')
  INSERT INTO #temp1 ([name])
  VALUES ('name4')
  INSERT INTO #temp2 ([name])
  VALUES ('Othername1')
  INSERT INTO #temp2 ([name])
  VALUES ('Othername2')
  
  INSERT INTO #temp2 (name)
  SELECT name FROM #temp1
  WHERE id NOT IN (SELECT id FROM #temp2)
  
  SELECT * FROM #temp1
  SELECT * FROM #temp2
  
  DROP TABLE #temp1
  DROP TABLE #temp2
  

  results in

  temp1

  id name
  
  1 name1
  2 name2
  3 name3
  4 name4
  

  temp2

  id name
  
  1 Othername1
  2 Othername2
  3 name3
  4 name4