sql-server - 查找 IN 表达式中不存在记录的 id

Question

任何人都可以帮我解决如何解决我时不时遇到但往往要解决很长一段路的常见问题吗？我正在尝试找到一种优雅的方法来识别给定大量值列表的表中不存在的 Id。

我收到了一份 Excel 工作表，其中包含 4000 多个字符串 ID 的列表，我需要确定其中哪些在 SQL Server 表中不存在。

我的第一个操作是使用 Notepad++ 并将列表转换为类似于 'XX1'、'XX2'、'XX3' 等的 CSV 列表，然后运行 ​​SELECT * FROM [TABLE] WHERE [ID] IN ('XX1 '，'XX2'，'XX3'，...)。这显示的记录比 Excel 中显示的记录少 2 条，因此现在我需要筛选它们以确定哪两条记录不在数据库中。

感谢您的帮助。

保罗。

Answer 1

您可以连接使用表值构造函数生成的内联表，前提是它不会因太多 id 而溢出，在这种情况下，您需要将它们转储到表中。

根据 the documentation，一个 VALUES 表达式中的行数限制为 1000 :

The maximum number of rows that can be constructed by inserting rows directly in the VALUES list is 1000

要与内联表连接，请使用以下语法:

INNER JOIN (VALUES (1), (2), ...) AS inline (id)

这是一个示例，其中包含此表(您的实际表):

CREATE TABLE databasetable (ID INT NOT NULL)
INSERT INTO databasetable VALUES (1), (2), (3), (5), (6), (8), (9), (10)

您可以像这样查询缺失值:

SELECT
    excel.id
FROM
    databasetable AS A
    RIGHT JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) AS excel (id)
        ON A.id = excel.id
WHERE
    A.id IS NULL

因此，您必须从 Excel 电子表格生成VALUES (1), (2), (3), ...)。

要使用临时表(如果行数超过 1000 行):

DECLARE @EXCEL TABLE (ID INT)
INSERT INTO @EXCEL VALUES (1), (2), (3), ..... -- first 1000
INSERT INTO @EXCEL VALUES (1), (2), (3), ..... -- next 1000
INSERT INTO ...                                -- and so on

SELECT
    @excel.id
FROM
    databasetable AS A
    RIGHT JOIN @excel
        ON A.id = @excel.id
WHERE
    A.id IS NULL