使用自增主键SQL插入多行

Question

INSERT INTO TABLE1 (COLUMN1, PRIMARY_KEY)
   SELECT 
      COLUMN1,
      (SELECT COALESCE(MAX(PRIMARY_KEY), 0) 
        FROM TABLE1) + 1 
   FROM 
      TABLE2

错误：

主键约束冲突。无法在对象中插入重复的键。

如何使主键在第一行之后递增？

我希望能够同时向此表添加一系列项目，而不是逐个插入它们。

感谢您的帮助。

- weewa

Answer 1

INSERT INTO TABLE1 (COLUMN1, PRIMARY_KEY)
SELECT COLUMN1,
       (SELECT COALESCE(MAX(PRIMARY_KEY),0)
       FROM TABLE1) + row_number() over (order by 1/0)
FROM TABLE 2

仅依靠这个语句，ID将是连续的，例如如果Max(Primary Key)为99并插入4条记录，则它们将是100、101、102、103。如果多个进程同时插入，很容易产生约束违规，但这并不意味着它比使用MAX()插入单个记录更糟糕，后者本质上是不安全的。

- RichardTheKiwi

---

@richardthekiwi 我明白MAX(Primary_key)可以找到现有的最大值，然后你为每个相应的示例添加1、2、3、4。但是，你能解释一下over(order by 1/0)是做什么的吗？ - JsonStatham

@SelectDistinct row_number() 必须与 order by 搭配使用。如果缺少用于排序的好东西，表达式 "1/0" 会解析为无操作（未定义的内容），因此 row_number() 可以自由地为 SELECT 输出分配编号，并按任意顺序排列。 - RichardTheKiwi

Answer 2

你可以尝试这个方法：

DECLARE @CurrentPK INT
SELECT @CurrentPK(MAX(PRIMARY_KEY)

SELECT column1, ROW_NUMBER() OVER (ORDER BY column1) AS 'RowNumber'
INTO #temp
FROM Table2

INSERT INTO TABLE1
(COLUMN1, PRIMARY_KEY)
SELECT COLUMN1,@CurrentPK+RowNumber 
FROM #temp

当然，为了避免竞态条件，您应该将其放入事务中，并明确锁定同时发生的其他插入。您最好使用具有try-catch块以及事务处理的存储过程。请注意，在这种情况下，避免使用事务不是一个选项。如果您不明确使用事务，则会出现两个进程尝试使用相同的ID号的情况。实际上，这就是为什么不推荐使用获取最后ID号的方法，因为使用它很容易创建数据库问题。我知道您现在被困住了，但至少要学会不要在未来使用这种短视的反模式。

- HLGEM

---

你有相关文章可以推荐吗？我正在将这些数据写入我们使用/购买的程序中，他们的支持表示应该使用max()函数。所以在这个领域我很蒻狼，但是我希望能够更多地学习正确的方法。 - weewa

不需要使用临时表，你可以将其作为派生表连接到SELECT FROM Table2。但是这种方式更清晰。 - Alejandro B.

Answer 3

您不必检查最大键并逐个增加1。将其设置为IDENTITY（1,1）NOT NULL列，服务器会自动处理。然后使用以下代码：

INSERT INTO TABLE1 (COLUMN1)
SELECT COLUMN1
FROM TABLE 2

- Kaf

---

这是否意味着更改列设计？我没有权限这样做。Primary_Key必须是INT类型。 - weewa

主键可以是 INT，没有任何问题。同时将其设置为 IDENTITY(1,1)。是的，您必须更改列设计。"您的方法不被推荐。" - Kaf

我正在写入的表格是我们使用的程序所用的，我没有更改设计的选项。不幸的是，只能按照它的设置来处理。 - weewa

那么您将无法进行批量插入，只能逐个插入一条记录。 - Kaf