Horje

MS SQL Server - Microsoft SQL Server - Last Inserted Identity
Start
  SCOPE_IDENTITY() 
CREATE TABLE dbo.logging_table(log_id INT IDENTITY(1,1) PRIMARY KEY, 
                               log_message VARCHAR(255))

CREATE TABLE dbo.person(person_id INT IDENTITY(1,1) PRIMARY KEY, 
                        person_name VARCHAR(100) NOT NULL)
GO;

CREATE TRIGGER dbo.InsertToADifferentTable ON dbo.person  
AFTER INSERT  
AS
    INSERT INTO dbo.logging_table(log_message)
    VALUES('Someone added something to the person table')
GO;

INSERT INTO dbo.person(person_name)
VALUES('John Doe')  

SELECT SCOPE_IDENTITY();

This will return the most recently added identity value produced on the same connection, within the current scope. In this case, 1, for the first row in the dbo.person table.
  Last Inserted Identity
  IDENT_CURRENT('tablename') 
SELECT IDENT_CURRENT('dbo.person');

This will select the most recently-added identity value on the selected table, regardless of connection or scope.
  @@IDENTITY and MAX(ID) 
SELECT MAX(Id) FROM Employees -- Display the value of Id in the last row in Employees table.
GO
INSERT INTO Employees (FName, LName, PhoneNumber) -- Insert a new row 
VALUES ('John', 'Smith', '25558696525') 
GO  
SELECT @@IDENTITY 
GO  
SELECT MAX(Id) FROM Employees -- Display the value of Id of the newly inserted row.  
GO

The last two SELECT statements values are the same.

 

Read Article
https://develop.horje.com/learn/1434/reference

 

Horje