TSQL 插入难题

Question

我有一张桌子。 该表需要存储有关位置的数值，因此最初我只有两列，没有递增列，给出以下内容：

RefID | TypeID
    1 | 1
    1 | 3
    1 | 6
    2 | 3
    3 | 5
    3 | 6

其中第一列是位置的引用，第二列是实际值。

问题是我如何决定给第一列赋予哪个值。 我的想法是添加一个自动递增字段来生成这些值，这样我就可以得到以下数据：

ID | RefID | TypeID
1  | 1   | 1
2  | 1   | 3
3  | 1   | 6
4  | 4   | 3
5  | 5   | 5
6  | 5   | 6

因此，自动编号列（第 1 列）充当参考列的种子。

所以我有两个问题 - 将标识列值复制到引用列，并将引用值返回给应用程序，以便在该位置有多个值时可以使用它。

我想出了这个存储过程：

CREATE PROCEDURE [dbo].[AddCaseType]
(
    @TypeID INTEGER,
    @CaseID INTEGER = NULL OUT 
)
AS
BEGIN

    INSERT INTO
        dbo.CaseTypeList(RefID, TypeID)
    VALUES
         ( ISNULL(@CaseID,SCOPE_IDENTITY()), @TypeID)

    Set @CaseID =  SCOPE_IDENTITY()
END
GO

ISNULL 检查 CaseID 上是否为 NULL，如果为 null 则使用 SCOPE_IDENTITY() 值。 然而，SCOPE_IDENTITY 在插入之前进行评估，因此返回生成的最后一个标识，而不是新的标识。

我无法使用相关表中的唯一值，因为如果用户编辑值，我需要完整跟踪。

所以我知道我想做什么，但只是没有知识或经验去做。

因此，封装一下：如何为集合中的第一项生成唯一值并返回该值，以便我可以将该值重用于该集合的其余部分？

另外，您也可以回答是否还有另一个，更简单的方法。 请记住 - 我是 SQL 方面的新手，所以我需要了解为什么会发生某些事情，因为我将来可能需要更改它。

更新：喝完一杯咖啡后，我注意到代码中存在一个错误 - 更改为三列的结果，因此 [ID] 成为 INSERT 语句第一部分中的 RefID。 这也使一些代码无效，从而部分改变了我的问题的性质。 对于这可能造成的混乱，我们深表歉意。

Answer 1

当插入到具有 identity 列的表时，您无需为 identity 列指定任何内容：

DECLARE @ERR INT

INSERT INTO CaseTypeList (TypeID, CaseID) --Column1 is auto-number, skip it
VALUES (@TypeID, @CaseID)

-- capture error var and last inserted identity value
SELECT @ERR = @@ERROR, @ID = SCOPE_IDENTITY()

-- IF @ERR <> 0 handle error, otherwise return

您不需要标识插入或类似的内容，除非您想要明确控制数量。 在这种情况下，您不会将其设置为identity。

另外，我对你的专栏有所猜测。

---

编辑：好的，看来您实际上根本不需要身份列。 相反，如果未提供，您需要生成序列中的下一个数字。 在这种情况下，您可以将身份列保留在那里 - 它不会造成任何损害，但如果您想要一个唯一键，可能会有所帮助：

CREATE PROCEDURE [dbo].[AddCaseType]
(
    @RefID     INTEGER = NULL OUT,
    @TypeID    INTEGER
)
AS

BEGIN TRANSACTION

  IF @RefID IS NULL BEGIN
    SELECT @RefID = MAX(RefID)+1 FROM CaseTypeList
  END

  IF @@ERROR <> 0 BEGIN ROLLBACK; RAISERROR('Could not get ID', 16, 1) END


  INSERT INTO
    CaseTypeList(RefID, TypeID)
  VALUES
    (@RefID, @TypeID)

  IF @@ERROR <> 0 BEGIN ROLLBACK; RAISERROR('Could not insert', 16, 1) END

COMMIT TRANSACTION 

注意：如果不能，您可能应该在 RefID 和 TypeID 上有一个唯一键约束是重复的。