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');
Tags:
SQL Advance