What is the difference between scope_identity and @@identity?

Both are used to get the last value generated in the identity column of the table.

@@IDENTITY function returns the last identity value generated within the current session, regardless of the scope.
This will return a value generated by an INSERT statement in a trigger, stored procedure or batch of T-SQL statements.


-- Insert into Table1 INSERT INTO Table1 (Column1) VALUES ('Value1'); -- Get the last identity value generated in the current session (which could be from Table1 or any other table) DECLARE @LastID INT; SET @LastID = @@IDENTITY; -- Insert into Table2 using the last identity value (which may not be from Table1) INSERT INTO Table2 (Table1ID, Column2) VALUES (@LastID, 'Value2');


The scope_identity() function returns the last identity created in the same session and the same scope. 

-- Insert into Table1
INSERT INTO Table1 (Column1) VALUES ('Value1');

-- Get the last identity value generated in Table1's scope
DECLARE @LastID INT;
SET @LastID = SCOPE_IDENTITY();

-- Insert into Table2 using the value from Table1
INSERT INTO Table2 (Table1ID, Column2) VALUES (@LastID, 'Value2');
 

 

Post a Comment

Previous Post Next Post