sql - 使用 SELECT 进行 INSERT INTO 并增加列中的值

Question

我正在尝试将缺失的行插入表中。其中一列是 OrderNumber(排序编号)，此列应为表中 sID 返回的 OrderNumber 最大值的 +1 。某些 sID 不会出现在 SPOL 表中，这就是语句末尾有 WHERE 子句的原因。我将再次运行此语句，但对于表中当前不存在 sID 的记录，将 OrderNumber 设置为 1。

由于OrderNumber导致主键sID + OrderNumber出现问题，下面的语句不起作用。

如何让基于 sID 列插入的每一行的 OrderNumber 增加？

INSERT INTO SPOL(sID, OrderNumber, oID)
   SELECT           
      sID, OrderNumber, oID
   FROM
      (SELECT 
           sID, 
           (SELECT Max(OrderNumber) + 1
            FROM SPOL
            WHERE sID = TMPO.sID) AS OrderNumber, 
           oID
       FROM TMPO
       WHERE NOT EXISTS (SELECT * FROM SPOL
                         WHERE SPOL.oID = TMPO.oID)
      ) AS MyData
   WHERE
       OrderNumber IS NOT NULL  

Answer 1

最好在数据库设计中使用标识列来处理这个问题 - 您没有提到是否可以更改架构，但希望您可以，因为如果您不必这样做，查询最终会变得更加干净自己管理。

您可以在 SQL Server Management Studio 中将 OrderNumber 列的 Identity 属性设置为 on，但它生成的脚本会使用新规范克隆表，插入您在 Identity_Insert 打开时已经获得的值，删除原始值表，并重命名临时表来替换它 - 这会产生巨大的开销，具体取决于您有多少行。

最有效的方法可能是:

1. 创建一个带有标识属性的附加列
2. 复制值
3. 重命名原始列
4. 将新列重命名为与原始列相同的名称
5. 删除原始 OrderNumber 列

一旦完成，它就完成了 - 并且会照顾好自己。难道你不希望你的插入语句简单地说这样的话:

INSERT INTO SPOL (sID, oID)
SELECT sID, oID,
FROM   TMPO
WHERE  OrderNumber IS NOT NULL