Updating a table with missing records from another table

Question

Hi, I am running a query that returns records from the LEFT of the EXCEPT that are not on the right query;

USE AdventureWorks;
GO
SELECT ProductID 
FROM Production.Product
EXCEPT
SELECT ProductID 
FROM Production.WorkOrder;

Lets say there are 6 records returned (there are 6 records in Production.Product table, that are not in Production.WorkOrder)

How would I write the query to update the 6 records into Production.WorkOrder table?

Answer 1

insert into workorder (productid)
select productid from product where productid not in (select productid from workorder)

This will insert into workorder all the productid's in the product table that aren't already in workorder.

Answer 2

I'd use a left join, like this:

USE AdventureWorks;
GO
SELECT p.ProductID
  FROM Production.Product p
  LEFT
  JOIN Production.WorkOrder wo
    ON p.ProductID = wo.ProductID
 WHERE wo.ProductID IS NULL;

This will return all the ProductID values from Product that do not appear in WorkOrder. The problem with the other answer (WHERE NOT IN) is that the sub-query will execute once/row, and if the tables are large, this will be really slow. A LEFT JOIN will only execute once, and then SQL will match the rows up - on a small table, there won't be much difference in practice, but on a larger table or in a production database, the difference will be immense.

Answer 3

Just turn your query into an INSERT?

INSERT INTO Production.WorkOrder(ProductID, ...)
SELECT ProductID, ...
FROM Production.Product
EXCEPT
SELECT ProductID 
FROM Production.WorkOrder;