SQL insert statement with select

sqlsql-server-2005

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.

Best Answer

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