在将记录添加到SQL表时检测重复项

I have a weird situation. I'm trying to add records to a SQL table recursively.

我有一个奇怪的情况。我正在尝试递归地将记录添加到SQL表中。

Table structure (simplified):

表结构(简化):

TableA
Key1  Key2 Description

Suppose I already update TableA with some records:

假设我已经用一些记录更新了TableA:

Key1   Key2  Description
12345  98765 Name1
23456  87654 Name2
34567  76543 Name3
45678  65432 Name4

I need to grab all records by Key2 now and look them up in another table (TableB), but I don't need to update TableA if the combination of Key1 and Key2 already exists. So for example, if I find a record in TableB with Key2 = 87654 and Key1 = 15965, I should update TableA as follows:

我现在需要通过Key2获取所有记录并在另一个表中查找它们(TableB),但如果Key1和Key2的组合已经存在,我不需要更新TableA。因此,例如,如果我在TableB中找到Key2 = 87654和Key1 = 15965的记录,我应该更新TableA,如下所示:

Key1   Key2   Description
87654  15965  Name59

But if I find a record in TableB with Key2 = 87654 and Key1 = 23456, I shouldn't create record in TableA at all:

但是如果我在TableB中找到Key2 = 87654和Key1 = 23456的记录,我根本不应该在TableA中创建记录:

Key1  Key2  Description
87654 23456 Name59

The above record should not be created.

不应创建上述记录。

Here's my query:

这是我的查询:

insert into TableA
(key1, key2, Description)
select Key1, Key2, Description from TableB
join TableB TableB.Key1 = TableA.key2
and TableB.Key2 <> TableA.Key1
and not exists(select 1 from TableA where TableA.Key2= TableB.Key1and TableA.Key1 = TableB.Key2)

This actually inserts duplicates so at the end this is what I get:

这实际上插入了重复项,所以最后这是我得到的:

Key1   Key2  Description
12345  98765 Name1
23456  87654 Name2
34567  76543 Name3
45678  65432 Name4
76543  74185 Name5
87654  82563 Name6
87654  23456 Name7
76543  34567 Name8
65432  45678 Name9

I'm trying to avoid inserting the last 3 records. Anyone has an idea what I'm doing wrong?

我试图避免插入最后3条记录。任何人都知道我做错了什么?

edit: Just to clarify...here's my dataset:

编辑:只是为了澄清......这是我的数据集:

Key1                                    Key2
E7483934-9AF7-E111-9912-78E7D16510D0    8FDE361D-B5C6-E011-A943-78E7D1644F78
E7483934-9AF7-E111-9912-78E7D16510D0    5001D99D-966B-E111-8FD2-78E7D16510D0
E7483934-9AF7-E111-9912-78E7D16510D0    76E8758E-A366-E111-B7C5-78E7D16510D0
E7483934-9AF7-E111-9912-78E7D16510D0    FB6F054F-E2BE-E511-9410-005056B5201F
E7483934-9AF7-E111-9912-78E7D16510D0    4A2007DA-E2BE-E511-9410-005056B5201F
E7483934-9AF7-E111-9912-78E7D16510D0    AE90299F-FAF5-E211-8706-D8D385B829F8
E7483934-9AF7-E111-9912-78E7D16510D0    76E9758E-A366-E111-B7C5-78E7D16510D0
E7483934-9AF7-E111-9912-78E7D16510D0    EB483934-9AF7-E111-9912-78E7D16510D0
8FDE361D-B5C6-E011-A943-78E7D1644F78    AE90299F-FAF5-E211-8706-D8D385B829F8
8FDE361D-B5C6-E011-A943-78E7D1644F78    EB483934-9AF7-E111-9912-78E7D16510D0
8FDE361D-B5C6-E011-A943-78E7D1644F78    76E9758E-A366-E111-B7C5-78E7D16510D0


AE90299F-FAF5-E211-8706-D8D385B829F8    8FDE361D-B5C6-E011-A943-78E7D1644F78
76E9758E-A366-E111-B7C5-78E7D16510D0    8FDE361D-B5C6-E011-A943-78E7D1644F78
EB483934-9AF7-E111-9912-78E7D16510D0    8FDE361D-B5C6-E011-A943-78E7D1644F78

So the top portion is TableB (my source table). I need to look this table up by specific Key1 value (say WHERE TableB.key1 = 'E7483934-9AF7-E111-9912-78E7D16510D0'), pull the records with that value into another table (TableA), then again look up TableB but by TableB.key1 = TableA.key2 and if the records exist add them to TableA table. The caveat is I don't want TableA to have the same combination of key1+key2. So if you look at the above example, I only want the first 11 records pulled into TableA, but it also additionally pulls in the last 3 which I don't need because those combinations already exist in TableA.

所以顶部是TableB(我的源表)。我需要通过特定的Key1值来查看这个表(比如WHERE TableB.key1 ='E7483934-9AF7-E111-9912-78E7D16510D0'),将带有该值的记录拉到另一个表(TableA)中,然后再次查找TableB但是通过TableB.key1 = TableA.key2,如果存在记录,则将它们添加到TableA表中。需要注意的是,我不希望TableA具有key1 + key2的相同组合。因此,如果你看一下上面的例子,我只希望将前11条记录拉入TableA,但它还会提取我不需要的最后3条记录,因为这些组合已经存在于TableA中。

TIA,
-Tony.

2 个解决方案

#1

You can use the conditions in the join clause to not insert unwanted records.

您可以使用join子句中的条件来不插入不需要的记录。

insert into TableA
(key1, key2, Description)
select b.key1, b.key2, b.description 
from TableB b
join TableA a on (b.key1 <> a.key1 and b.key1 <> a.key2)
              or (b.key2 <> a.key1 and b.key2 <> a.key2)

#2

Don't use joins, or use them only to negate the records you want to insert.

不要使用连接,或仅使用连接来否定要插入的记录。

When using joins, the database will fetch all records from TableB, compare with each record of TableA and if there's a single one that validates it, it'll pass. Following your example, the unwanted row could be validated by the first row of table A, for example (in your example you are even joining TableB with TableB... I assume the joined is TableA).

使用联接时,数据库将从TableB获取所有记录,与TableA的每个记录进行比较,如果有一个记录验证它,它将通过。按照您的示例,可以通过表A的第一行验证不需要的行(例如,在您的示例中,您甚至将TableB与TableB连接...我假设连接是TableA)。

Not using the joins based on your query:

不使用基于您的查询的联接:

insert into TableA
(key1, key2, Description)
select Key1, Key2, Description from TableB as B
where not exists(select 1 from TableA as A where A.Key2=B.Key1 and A.Key1=B.Key2)

Or using joins to negate/block unwanted rows:

或者使用连接来否定/阻止不需要的行:

insert into TableA
(Key1, Key2, Description)
select distinct B.Key1, B.Key2, B.Description 
from TableB as B
left outer join TableA as A on A.Key2=B.Key1 and A.Key1=B.Key2
where A.Key1 is null

Considering that Key1 may not be null. Also use distinct to prevent records that match more than one row from being copied.

考虑到Key1可能不为null。还可以使用distinct来防止复制匹配多行的记录。