SQL insert statement with select

Question

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.

Answer 1

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)

Answer 2

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

Answer 3

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!

Answer 4

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!

Answer 5

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

Answer 6

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