How to Retrieve the Last Inserted ID in SQL Server?

Obtaining the ID of the Last Inserted Row

When working with SQL Server databases and auto-incrementing primary keys, accessing the ID of the most recently added record is a common requirement. This guide outlines several methods to achieve this, considering different SQL Server versions and the potential presence of insert triggers.

SQL Server 2005 and Later (Without Insert Triggers)

For versions 2005 and beyond, a streamlined approach involves incorporating the OUTPUT INSERTED.ID clause directly into your INSERT statement. This clause efficiently returns the ID of the newly inserted row. Here's an example:

INSERT INTO aspnet_GameProfiles (UserId, GameId)
OUTPUT INSERTED.ID
VALUES (@UserId, @GameId);

The newly generated ID is then readily accessible via the ExecuteScalar() method of your SqlCommand object:

Int32 newId = (Int32)myCommand.ExecuteScalar();

SQL Server 2000 or When Insert Triggers Exist

In SQL Server 2000 or scenarios involving insert triggers, a slightly different strategy is necessary:

1. Execute the standard INSERT statement.
2. Employ the SCOPE_IDENTITY() function to retrieve the last inserted identity value.

INSERT INTO aspnet_GameProfiles (UserId, GameId)
VALUES (@UserId, @GameId);
SELECT SCOPE_IDENTITY();

Similar to the previous method, ExecuteScalar() can be used to obtain the resulting ID:

Int32 newId = (Int32)myCommand.ExecuteScalar();

These methods provide efficient and reliable ways to retrieve the ID of the last inserted row, enabling seamless integration with subsequent database operations.

The above is the detailed content of How to Retrieve the Last Inserted ID in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!