How to Retrieve the Last Inserted ID After a Database Insertion in SQL Server?

Get the latest ID after inserting into SQL Server database

After inserting data into the database, it is often necessary to obtain the ID of the latest inserted record. Let's look at a scenario: After inserting a row into the aspnet_GameProfiles table, we need to obtain the automatically incremented GamesProfileId.

The following code snippet executes an INSERT statement but has no mechanism to capture the newly generated GamesProfileId. To achieve this, we will explore different methods based on the version of SQL Server used.

SQL Server 2005 and above (no insert trigger)

For SQL Server 2005 and above, if there is no insert trigger defined on the aspnet_GameProfiles table, you can modify the insert statement to include an OUTPUT clause:

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

This will automatically return the inserted ID as the output of the query. You can retrieve it using the following code:

<code class="language-csharp">SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
...
SqlDataReader reader = myCommand.ExecuteReader();
reader.Read();
Int32 newId = reader.GetInt32(0);</code>

SQL Server 2000 or insert trigger

If you are using SQL Server 2000 or have insert triggers on the table, you can use the SCOPE_IDENTITY() function:

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

After executing the query, you can retrieve the latest inserted ID using the following code:

<code class="language-csharp">SqlCommand myCommand = new SqlCommand(insertSql, myConnection);
...
Int32 newId = (Int32) myCommand.ExecuteScalar();</code>

By using these techniques, you can effectively obtain the last inserted GamesProfileId, thereby being able to use it in your application for subsequent insertions or other operations.

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