有效地将行的非重复变体插入同一个表中（除了/不存在？）

发布于 2025-01-18 01:42:01 字数 646 浏览 1 评论 0原文

我有一个过程会生成许多潜在的不对称结果（记录 A 链接到记录 B，但记录 B 可能不会链接到记录 A）。这些结果中的每一个都存储在一个表中，我想将所有缺失的链接插入到同一个表中（即为记录 B 链接到记录 A 的每种情况生成一行） - 而不生成重复项。

环顾四周，似乎 NOT EXISTS 是首选方法。但由于这是对同一个表的插入，我想看看是否有人有更有效的方法的想法（表大小从〜50,000到〜20,000,000不等）。

INSERT INTO [table1]
([record_id], [linked_record_id], [flag_value])
SELECT
,[linked_record_id] AS [record_id]
,[record_id]        AS [linked_record_id]
,[flag_value]
FROM [table1] AS A
WHERE [flag_value] = 1
AND NOT EXISTS (
    SELECT
    [record_id]
    ,[linked_record_id]
    ,[flag_value]
    FROM [table1] AS B
    WHERE [flag_value] = 1
    AND A.[linked_record_id] = B.[record_id]
    )

原文

I have a process which generates a number of potentially asymmetric outcomes (record A links to record B, but record B may not link to record A). Each of these outcomes are stored in a table and I want to insert into that same table all the missing links (i.e. generate a row for every case where record B links to record A) - without generating duplicates.

From looking around it seems that NOT EXISTS is the preferred method for this. But as this is an INSERT into the same table, I wanted to see if anyone had ideas for a more efficient approach (table size will vary from ~50,000 to ~20,000,000).

INSERT INTO [table1]
([record_id], [linked_record_id], [flag_value])
SELECT
,[linked_record_id] AS [record_id]
,[record_id]        AS [linked_record_id]
,[flag_value]
FROM [table1] AS A
WHERE [flag_value] = 1
AND NOT EXISTS (
    SELECT
    [record_id]
    ,[linked_record_id]
    ,[flag_value]
    FROM [table1] AS B
    WHERE [flag_value] = 1
    AND A.[linked_record_id] = B.[record_id]
    )

分享到QQ

分享到微博