SQL Server - 如何在多行插入事务中手动递增主键？

Question

我在使用SQL Server。 我有一个包含PK int列的表。 该列没有启用自动增量，并且不允许我更改架构。 我需要手动插入大量行（可能达到数千行）到这个表中。 所有插入的数据都不来自任何现有的表。 然而，我需要确保每一行PK列都会递增+1。 我当前的脚本如下：

BEGIN TRAN
    INSERT INTO DB1.dbo.table1
    (PK_col, col1)
    VALUES
    (10, 'a')
    ,(11, 'something')
    ,(12, 'more text')
    ;

我已经通过预查询（SELECT MAX(PK_col) + 1）知道PK_col当前为9。

我的问题是确保每个新行的PK列增加+1。因为可能要插入数千行，我想减少跳过值或抛出PK约束违规的可能性。我知道我可以在DB之外（通过Excel）实现这一点，只要在运行SQL脚本之前验证PK值即可。但是，我想创建一个处理TRAN语句本身自动递增的解决方案。这可能吗（不会遇到竞争条件）？如果可以，如何实现？

- skyline01

---

是的，这是可能的。到目前为止最简单的解决方案是使用身份验证。它将为您完成所有这些事情，而您完全不需要付出任何努力。 - Sean Lange

当我不被允许更改表模式时，如何使用标识(identity)？ - skyline01

你可以根据你使用的SQL Server版本来使用一个序列。https://msdn.microsoft.com/en-us/library/ff878091.aspx 这是你经常需要做的事情吗？听起来像是我会推动将表更改为具有标识，但我当然不知道所有细节。 - Sean Lange

不，我并不经常更新表格。可能一个月一次。而且，实际上并不是我来更新的。我的其他同事会进行更新，但我不信任他们的SQL技能或数据验证工作。（这是完全不同的问题。）我可以推动在表格上设置身份标识，但我的建议总是被忽视。 - skyline01

我理解你因为建议被忽视而感到的痛苦。通常情况下，我会建议与我真正想要的相反，因为几乎总是发生相反的事情。无论是使用序列还是Gordon建议的更简单的方法，都是我会选择的。 - Sean Lange

Answer 1

以下代码应该可以实现您想要的功能：

INSERT INTO DB1.dbo.table1(PK_col, col1)
    SELECT COALESCE(l.max_pk_col, 0) + row_number() over (order by (select null)) as PK_col,
           col1
    FROM (VALUES ('a'), ('something'), ('more text')) v(col1) CROSS JOIN
         (SELECT MAX(pk_col) as max_pk_col FROM DB1.dbo.table1) l;

在这种情况下，你需要小心处理。如果有其他操作可能会更新表格的话，锁定整个表格以完成INSERT是一个不错的选择。

- Gordon Linoff

Answer 2

一种方法是创建一个带有标识列和数据列的新临时表，进行插入操作，然后将此表的内容插入回所需的原始表中。然后清理它。

BEGIN TRAN

DECLARE @initialIndex INT

SELECT @initialIndex = MAX(PK_col) + 1)
FROM DB1.dbo.table1

CREATE TABLE #tempData(
  PK_col INT IDENTITY(@initialIndex, 1),
  col1 VARCHAR(MAX)
)

INSERT INTO #tempData (col1)
VALUES
('a')
,('something')
,('more text')

INSERT INTO DB1.dbo.table1
SELECT PK_col, col1
FROM #tempData

DROP TABLE #tempData

- Hamburglar