How to Retrieve the Last Inserted ID in SQL Server After an INSERT Statement?

Accessing the Newly Created Record's ID in SQL Server

This guide demonstrates how to obtain the ID of the most recently inserted record into the aspnet_GameProfiles table within SQL Server. The optimal method depends on your SQL Server version:

SQL Server 2005 and Later (Without INSERT Triggers):

The most efficient approach for newer SQL Server versions is to leverage the OUTPUT clause directly within the INSERT statement:

<code class="language-sql">INSERT INTO aspnet_GameProfiles (UserId, GameId)
OUTPUT INSERTED.ID
VALUES (@UserId, @GameId);</code>

Subsequently, retrieve the generated ID using ExecuteScalar():

<code class="language-csharp">Int32 newId = (Int32)myCommand.ExecuteScalar();</code>

This method avoids an extra query, improving performance.

SQL Server 2000 or When Using INSERT Triggers:

For older SQL Server versions or scenarios involving INSERT triggers, employ the SCOPE_IDENTITY() function:

<code class="language-sql">INSERT INTO aspnet_GameProfiles (UserId, GameId)
VALUES (@UserId, @GameId);
SELECT SCOPE_IDENTITY();</code>

The retrieved ID can then be accessed using ExecuteScalar():

<code class="language-csharp">Int32 newId = (Int32)myCommand.ExecuteScalar();</code>

By using either of these methods, you can seamlessly retrieve the newly generated ID from aspnet_GameProfiles, enabling subsequent operations that require this information.

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