SQL Server-如何在多行INSERT事务中手动增加PK

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语句本身中处理自动增量。这可能吗(在没有比赛的情况下)？如果是，如何？

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期间锁定整个表可能是个好主意——如果有其他事情可以更新表的话。

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