CODEWITHSUNDEEP
sqlms-accessvba
LuckyLuke82
LuckyLuke82

Reputation: 604

Access SQL - INSERT unmatched records

I have 3 tables - Table1 & Table2 where there are records, and Table3 where their differences should be Inserted. Here's what I have now :

SQL = "INSERT INTO Table3 (Field1,Field2)" & _
      "SELECT Table2.Field1, Table2.Field2" & _
      " FROM Table2 LEFT JOIN Table1 ON Table2.Field1 = Table1.Field1" & _
      " OR Table2.Field2 = Table1.Field2" &_ 
      " WHERE (((Table1.Field1) Is Null) OR ((Table1.Field2) Is Null))" & _
      " AND NOT (Table2.Field1 IS NULL AND Table2.Field2 IS NULL)"

This query successfully inserts unmatching records in Table3, but when I run this query once again and there shouldn't be Inserted anything anymore, query still inserts 1 record in Table3 where one of the fields in Table1 or Table2 is Null.

How can I eliminate this from happening, where's the flaw ?

EDIT : Here is sample from Tables....

Table1:
CompanyNo    CompanyName
111          Microsoft 
             Apple
333          Oracle

Table2:
CompanyNo    CompanyName
111          Microsoft
222          Apple
333          Oracle
             Intel
555          Google

Then Table3 should result differences:
CompanyNo    CompanyName
222          Apple
             Intel
555          Google

And on first run of code result is correct, but when I run It again, "Intel" record is shown again - but It shouldn't because all new data from Table2 is allready in Table1 (I'm doing INSERT and UPDATE to Table1 after this Query, so "Intel" record is allready in Table1 then).

Upvotes: 0

Views: 1327

Answers (3)

ehh
ehh

Reputation: 3480

I prepared a small example in order to be sure we are on the same page.

enter image description here

If this is what you are looking for so you can use UNION ALL.

INSERT INTO Table3
SELECT *
FROM (SELECT Table1.Field1 , Table1.Field2 FROM Table1 LEFT JOIN Table2 ON     
Table1.Field1 = Table2.Field1 WHERE Table2.Field1 is Null 
UNION ALL 
SELECT Table2.Field1 , Table2.Field2 FROM Table2 LEFT JOIN Table1 ON     
Table1.Field1 = Table2.Field1 WHERE Table1.Field1 is Null 
)  AS UnMatchedTable;

As I said before, running it again will add the same records again.

Upvotes: 0

Parfait
Parfait

Reputation: 107652

Consider splitting up the append queries into two actions, capturing Table 1 and Table 2 differences respectively. Right now, you are attempting to combine both differences and the empty insert is differences lying on the non-referenced select columns from Table 1:

table1diffSQL = "INSERT INTO Table3 (Field1,Field2)" & _
                "SELECT Table1.Field1, Table1.Field2" & _
                " FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1" & _
                " AND Table1.Field2 = Table2.Field2" & _  
                " WHERE ((Table2.Field1) Is Null OR (Table2.Field2) Is Null)"


table2diffSQL = "INSERT INTO Table3 (Field1,Field2)" & _
                "SELECT Table2.Field1, Table2.Field2" & _
                " FROM Table2 LEFT JOIN Table1 ON Table1.Field1 = Table2.Field1" & _
                " AND Table1.Field2 = Table2.Field2" & _
                " WHERE ((Table1.Field1) Is Null OR (Table1.Field2) Is Null)"

EDIT (per OP's sample data)

SELECT Table2.CompanyNo, Table2.CompanyName
FROM Table2 LEFT JOIN Table1
ON  Table1.CompanyNo = Table2.CompanyNo
AND Table1.CompanyName = Table2.CompanyName
WHERE Table1.CompanyNo Is Null OR Table1.CompanyName Is Null

Results

CompanyNo   CompanyName
      222   Apple
            Intel
      555   Google

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You only want the fields from Table2, so I would use not exists:

select t2.Field1, t2.Field2
from table2 as t2
where not exists (select 1
                  from table1 as t1
                  where (t1.field1 = t2.field1 or t1.field1 is null and t2.field1 is null) or
                        (t1.field2 = t2.field2 or t1.field2 is null and t2.field2 is null)
                 );

You might get similar results if you move the NULL comparison logic to the on clause. But that might not be possible in MS Access.

Upvotes: 0

Related Questions