Getting the ID of the Last Inserted Record in SQL Server

In SQL Server, you can get the ID of the last inserted record using the `SCOPE_IDENTITY()` function or the `IDENT_CURRENT()` function. Here's how to do it.

Using SCOPE_IDENTITY()

The `SCOPE_IDENTITY()` function returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.

INSERT INTO YourTableName (Column1, Column2, ...) VALUES ('Value1', 'Value2', ...); SELECT SCOPE_IDENTITY() AS LastInsertedID; 

This query inserts a new record into the table and then returns the ID of the last inserted record using `SCOPE_IDENTITY()`.

Using IDENT_CURRENT()

The `IDENT_CURRENT()` function returns the current identity value for a specified table in any session and any scope.

INSERT INTO YourTableName (Column1, Column2, ...) VALUES ('Value1', 'Value2', ...); SELECT IDENT_CURRENT('YourTableName') AS LastInsertedID; 

This query inserts a new record into the table and then returns the ID of the last inserted record using `IDENT_CURRENT()`.

Example

Let's say we have a table `Employees` with an `ID` column that is an identity column:

CREATE TABLE Employees ( ID INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(50), Department NVARCHAR(50) ); INSERT INTO Employees (Name, Department) VALUES ('John Doe', 'Sales'); SELECT SCOPE_IDENTITY() AS LastInsertedID; 

This query inserts a new record into the `Employees` table and then returns the ID of the last inserted record using `SCOPE_IDENTITY()`. The result might be:

The result shows that the ID of the last inserted record is 1.

Using OUTPUT Clause

You can also use the `OUTPUT` clause to return the inserted ID:

INSERT INTO Employees (Name, Department) OUTPUT inserted.ID VALUES ('John Doe', 'Sales'); 

This query inserts a new record into the `Employees` table and returns the ID of the inserted record using the `OUTPUT` clause.