This query works, but is really slow. Is there a better way to write it? I have 2 tables, Card and Shipped. For all cards, I am trying to insert a record into the Shipped table if it they are not already there:

INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped)
SELECT Card.CardID,'Wpg',#1/1/2007#,0
FROM Card
WHERE Card.CardID NOT IN
(
SELECT Shipped.CardID
FROM Card LEFT JOIN Shipped ON Card.CardID=Shipped.CardID
WHERE SystemID= 10 AND ShippedFrom='Wpg' AND ShipDate=#1/1/2007#
)
AND SystemID=10

Maybe I'm talking rubbish but assuming that Cardid is unique in both tables then maybe if you let the system stop the duplicates it may be faster than what you are doing.

INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped)
SELECT Card.CardID,'Wpg',#1/1/2007#,0
FROM Card;


Brian

Try:
INSERT INTO SHIPPED (CardID, ShippedFrom, ShipDate, QtyShipped)
SELECT Card.CardID,'Wpg',#1/1/2007#,0
FROM Card LEFT JOIN
(SELECT Shipped.CardID
FROM Shipped
WHERE Shipped.ShippedFrom='Wpg'
AND Shipped.ShipDate=#1/1/2007#
) AS Shipped ON Card.CardID = Shipped.CardID
WHERE Card.SystemID = 10
AND Shipped.CardID IS NULL;

Thanks! That query dropped the time from 3 minutes to 10 seconds! Awesome! I had tried to use the is null before, but couldn't get it to work.