sql - 如何在没有序列的情况下自动增加 SQL Server 2016 合并插入中的主键？

Question

我正在编写一个查询，将数据从一个表导入到一个新表。我需要插入新表中不存在的记录，并更新存在的记录。我正在尝试使用 MERGE“upsert”方法。

由于客户的数据库和应用程序结构，我遇到了一些独特的问题。我要插入的表有一个“唯一 ID”字段，每个新行都会加 1，但该表不会自动递增； insert语句需要拉取目标表中最高的ID，并为新记录加1。

根据我的研究，我不知道如何使用 MERGE 来做到这一点。我没有创建序列的数据库权限。我已经尝试了很多方法，但目前我的查询如下所示:

MERGE
    dbo.targetTable as target
USING
    dbo.sourceTable AS source
ON
    target.account_no = source.account_ID

WHEN NOT MATCHED THEN
    INSERT (
        ID,
        FIELD1,
        FIELD2,
        FIELD3
) VALUES (
        (SELECT MAX(ID) + 1 FROM dbo.targetTable),
        'field1',
        'field2',
        'field3'
)

我在使用这段代码时遇到的问题是，它似乎只为新 ID 运行一次 select 语句。也就是说，如果目标表中的最高 ID 是 10，它将插入 ID 为 11 的每条新记录。这不起作用，因为我得到的是违反 PRIMARY KEY 约束。无法在对象中插入重复的键 错误。我已经进行了大量的谷歌搜索并尝试了不同的事情，但一直无法弄清楚这一点。感谢任何帮助，谢谢。

编辑:为了澄清，唯一 ID 列不会自动填充。如果我不为 ID 列插入值，我会得到 无法将 NULL 值插入表“dbo.targetTable”的“ID”列；列不允许为空。更新失败。

再次，正如我最初提到的，我没有创建序列的权限。它只是抛出一个错误并说我没有这样做的权限。

我同意将 ID 列更改为自动递增将是完美的，但我也没有能力像这样修改表。

Answer 1

如果您不需要 ID 连续，则可以将最后一个可用的 ID 添加到 ROW_NUMBER() 以生成新的、不重复的 ID。

BEGIN TRANSACTION

    DECLARE @NextAvailableID INT = (SELECT ISNULL(MAX(ID), 0) FROM dbo.targetTable WITH (TABLOCKX))

    ;WITH SourceWithNewIDs AS
    (
        SELECT
            S.*,
            NewID = @NextAvailableID + ROW_NUMBER() OVER (ORDER BY S.account_ID)
        FROM
            dbo.sourceTable AS S
    )
    MERGE
        dbo.targetTable as target
    USING
        SourceWithNewIDs AS source
    ON
        target.account_no = source.account_ID
    WHEN NOT MATCHED THEN
        INSERT (
            ID,
            FIELD1,
            FIELD2,
            FIELD3
    ) VALUES (
            NewID,
            'field1',
            'field2',
            'field3'
    )

COMMIT

请记住，此示例缺少回滚时的正确错误处理，并且用于检索最大 ID 的锁将阻止所有其他操作，直到提交或回滚。

如果您需要新行具有连续的 ID，那么您可以使用与常规 INSERT (使用 WHERE NOT EXISTS...)相同的方法，而不是使用MERGE(必须单独编写UPDATE)。