Correct SQL for Only Adding New Records? 1

[kjv1611]

I have a query that I think is working correctly. But I wanted to make sure for future reference that I am not doing something goofy. Following is the SQL for the query:

INSERT INTO tblOwnersRef ( OwnerID, Owner )
SELECT AllOwners20080807.OwnerID, AllOwners20080807.Owner
FROM AllOwners20080807 INNER JOIN tblOwnersRef ON AllOwners20080807.OwnerID = tblOwnersRef.OwnerID
WHERE (((tblOwnersRef.OwnerID) Is Null));

Currently, it is not returning any records, which I believe to be correct, but I want to be sure. Actually, new records may appear at some point, but I doubt it will be terribly often.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PWise]

INSERT INTO tblOwnersRef ( OwnerID, Owner )
SELECT AllOwners20080807.OwnerID, AllOwners20080807.Owner
FROM AllOwners20080807 [COLOR=red]INNER[/color] JOIN tblOwnersRef ON AllOwners20080807.OwnerID = tblOwnersRef.OwnerID
[COLOR=red]WHERE (((tblOwnersRef.OwnerID) Is Null))[/color red];

This query can never return records because it is a inner join so it must have a corresponding record in tblOwnersRef and in the where condition where it is null

What I think you want is this query

INSERT INTO tblOwnersRef ( OwnerID, Owner )
SELECT AllOwners20080807.OwnerID, AllOwners20080807.Owner
FROM AllOwners20080807 [COLOR=green]Left[/color] JOIN tblOwnersRef ON AllOwners20080807.OwnerID = tblOwnersRef.OwnerID
[COLOR=Green]WHERE (((tblOwnersRef.OwnerID) Is Null))[/color];

this won't insert into tblOwnersRef if the record is already there

 

[kjv1611]

Thanks a bunch! I thought of changing the join type like you suggested, but just didn't try it.

And actually it DID return some records. I think it's b/c the original data for this purpose is not pulled from the "master" owner table, so it actually is possible for new records to pop up now and then. It didn't return many records, but did a few.


--

"If to err is human, then I must be some kind of human!" -Me