重置 SCOPE_IDENTITY()

Question

我有一个存储过程，首先将一些数据插入到临时表中，然后再向另一个表中插入一行。我在第二次插入后调用Scope_Identity()来获取新插入记录的标识符。

如果由于连接的原因第二个插入没有执行任何操作，则我希望检查Scope_Identity并引发异常。但是Scope_Identity返回的是第二个插入之前从临时表插入的最后一个标识符。

是否有一种方法可以在调用第二个插入之前重置SCOPE_IDENTITY，或者有更好的方法来确定第二个插入实际上没有插入任何内容？

- Kenoyer130

Answer 1

在第二次插入后立即检查 @@ROWCOUNT。如果它为0，则没有插入任何行。

INSERT INTO YourTable
SELECT ...

IF (@@ROWCOUNT = 0)
BEGIN
RAISERROR('Nothing inserted',16,1)
RETURN
END

- Martin Smith

Answer 2

Martin Smith的回答完全回答了你的问题。

显然，这是互联网上唯一一个询问如何重置Scope_Identity()的页面。
我认为这对于任何使用T-SQL的人都是至关重要的。

我留下这个答案，供那些像我一样正在寻找前一个插入语句插入的标识（而不是最后一个成功的标识插入）的人使用。

以下是我的结论：

SET @SomeID = (CASE WHEN @@ROWCOUNT > 0 THEN SCOPE_IDENTITY() ELSE NULL END)

- MikeTeeVee

Answer 3

我认为其他回答可能更实用，但我想在这里记录我的发现，以防有一天能帮到某个人。（这是在 SQL Server 2005 中；不确定此行为是否存在于更新的版本中。）

该技巧的基础是利用以下属性（来自 Books Online 对 @@IDENTITY 的文档）：“完成 INSERT、SELECT INTO 或批量复制语句后……如果该语句未影响任何具有标识列的表，则 @@IDENTITY 返回 NULL。”虽然我找不到明确说明，但似乎这种行为也适用于 SCOPE_IDENTITY()。因此，我们完成一个不影响任何具有标识列的表的 INSERT 语句：

CREATE TABLE NoIdentity (notId BIT NOT NULL)

-- An insert that actually inserts sets SCOPE_IDENTITY():
INSERT INTO YourTable (name)
SELECT 'a'
WHERE 1 = 1 -- simulate a join that yields rows

SELECT @@identity, SCOPE_IDENTITY()
-- 14, 14 (or similar)

-- The problem: an insert that doesn't insert any rows leaves SCOPE_IDENTITY() alone.
INSERT INTO YourTable (name)
SELECT 'a'
WHERE 1 = 0 -- simulate a join that yields no rows

SELECT @@identity, SCOPE_IDENTITY()
-- Still 14, 14 . . . how do we know we didn't insert any rows?

-- Now for the trick:
INSERT INTO NoIdentity (notId)
SELECT 0
WHERE 1 = 0 -- we don't actually need to insert any rows for this to work

SELECT @@identity, SCOPE_IDENTITY()
-- NULL, NULL.  Magic!

INSERT INTO YourTable (name)
SELECT 'a'
WHERE 1 = 0 -- simulate a join that yields no rows

SELECT @@identity, SCOPE_IDENTITY()
-- Still NULL, NULL since we didn't insert anything.  But if we had, it would be non-NULL.
-- We can tell the difference!

所以，针对您的情况，似乎您可以这样做。

INSERT INTO NoIdentity (notId)
SELECT 0
WHERE 1 = 0

在执行第二个INSERT之前，需要重置SCOPE_IDENTITY()。

- Woody Zenfell III

Answer 4

经过考虑多种方案，我发现自己喜欢@BenThul在相关问题中的回答的一个变体：

DECLARE @result TABLE (id INT NOT NULL)

INSERT INTO YourTable (name)
OUTPUT INSERTED.id INTO @result (id)
SELECT 'a'
WHERE 1 = 0 -- simulate a join result

SELECT CASE
    WHEN (SELECT COUNT(1) FROM @result) = 1 THEN (SELECT TOP 1 id FROM @result)
    ELSE -1
END

正如您从我的最终 SELECT CASE... 中所看到的，在我的情况下，我试图得到一个单一的 INT NOT NULL，以帮助我理解是否插入了一行（在这种情况下，我想要它的 ID）或没有。 （如果可能的话，我不建议首先处于这种情况！）您将根据需要用 @result 做什么。

我喜欢 INSERT 和 @result 之间的关系是明确的，并且不太可能被其他我可能没有考虑的操作污染。 我还喜欢 @result 自然地处理了插入多行的情况。

- Woody Zenfell III

Answer 5

MikeTeeVee的回答，当与Martin-Smith的回答结合使用时非常强大。

以下是我的合并用法：

BEGIN TRY

INSERT INTO YourTable
SELECT ...

SELECT @SomeID = (CASE WHEN @@ROWCOUNT > 0 THEN SCOPE_IDENTITY() ELSE NULL END)

IF (@SomeID IS NULL)
BEGIN
    RAISERROR('Nothing inserted',16,1)
END

END TRY 

BEGIN CATCH
    /* Handle stuff here - In my case I had several inserts
       - some could not happen and I did not raise errors for them
       - Some had to make a Transaction to rollback 
    */
END CATCH

- AndVel