首页 文章

访问SQL - INSERT不匹配的记录

提问于
浏览 次
0

我有3个表 - 表1和表2中有记录，表3中应该插入差异 . 这就是我现在拥有的：

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)"

此查询在Table3中成功插入了不匹配的记录，但是当我再次运行此查询并且不应再插入任何内容时，查询仍会在Table3中插入1条记录，其中Table1或Table2中的一个字段为Null .

我怎样才能消除这种情况，哪些是缺陷？

编辑：这是表格的样本....

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

并且在第一次运行代码时结果是正确的，但是当我再次运行它时，“Intel”记录再次显示 - 但它不应该因为Table2中的所有新数据都已经在Table1中（我正在对Table1执行INSERT和UPDATE）在这个Query之后，所以“Intel”记录已经在Table1中了 .

sql ms-access access-vba

3 回答

  • 0

    你只需要 Table2 中的字段，所以我会使用 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)
                 );

    如果将 NULL 比较逻辑移动到 on 子句，则可能会得到类似的结果 . 但这在MS Access中可能无法实现 .

    回复于
  • 0

    考虑将追加查询拆分为两个操作，分别捕获表1和表2的差异 . 现在，您正在尝试将两个差异结合起来，而空插入是表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 （根据OP的样本数据）

    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

    结果

    CompanyNo   CompanyName
      222   Apple
            Intel
      555   Google
    回复于
  • 0

    我准备了一个小例子，以确保我们在同一页面上 .

    enter image description here

    如果这是您正在寻找的，那么您可以使用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;

    正如我之前所说，再次运行它会再次添加相同的记录 .

    回复于

相关问题