Reset SCOPE_IDENTITY()

Question

I have a stored procedure that first inserts some data into a temp table and then inserts a row into another table. I am calling Scope_Identity() after the second insert to pick up the newly inserted record Identity.

If the second insert does nothing due to a join, I want to check the Scope_Identity and raise an exception. But Scope_Identity is returning the last identity created from the temp table insert before the second insert.

Is there a way to reset SCOPE_IDENTITY before calling the second insert, or a better way to determine if the second insert didn't actually insert anything?

Answer 1

Check @@ROWCOUNT immediately after the 2nd insert. If it is 0 then no rows were inserted.

INSERT INTO YourTable
SELECT ...

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

Answer 2

Martin Smith's answer totally answers your question.

This is apparently the only page on the internet asking how to reset the Scope_Identity().
I believe this is vital for anyone working with T-SQL.

I am leaving this answer for anyone who came here (like me) looking for the identity that was inserted by the previous insert statement (and not the last randomly successful identity insert).

This is what I came up with:

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

Answer 3

I think other answers given may be more practical, but I did want to record my finding here in case it helps someone some day. (This is in SQL Server 2005; not sure whether this behavior persists in newer versions.)

The basis of the trick is an exploitation of the following property (from Books Online's documentation of @@IDENTITY): "After an INSERT, SELECT INTO, or bulk copy statement is completed . . . If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL." Although I can't find it explicitly stated, it appears that this behavior applies to SCOPE_IDENTITY() as well. So, we complete an INSERT statement that does not affect any tables with identity columns:

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!

So, for your case, it would seem that you could do

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

to reset SCOPE_IDENTITY() before performing your second INSERT.

Answer 4

Having considered several alternatives, I find myself liking a riff on @BenThul's answer to a related question:

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

As you can see from my final SELECT CASE..., in my situation I was trying to end up with a single INT NOT NULL that would help me understand whether a row was inserted (in which case I wanted its ID) or not. (I would not recommend being in this situation in the first place, if possible!) What you would do with @result depends on what you need to do.

I like that the relationship between the INSERT and @result is explicit and unlikely to be contaminated by other intervening operations I might not be thinking about. I also like that @result naturally handles cases with more than one row inserted.

Answer 5

MikeTeeVee's answer which I found when combined with the answer from Martin-Smith's answer is very powerful.

Here is my merged use:

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