SQL Server Queries - Get id Last inserted Record

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.