How to Retrieve the Last Inserted Row ID in SQL?

Accessing the ID of the Most Recently Added Row in SQL

After inserting new data into a database table, you often need to retrieve the automatically generated ID of the new row. This article shows several SQL methods for getting this ID, avoiding the need for temporary tables.

SCOPE_IDENTITY()

For tables with an INT or BIGINT column set as an identity column, SCOPE_IDENTITY() efficiently retrieves the ID following an INSERT statement. Here's how:

<code class="language-sql">INSERT INTO dbo.YourTable(columns....)
VALUES(..........)

SELECT SCOPE_IDENTITY()</code>

This returns the most recently inserted identity value within the current scope (which can be affected by nested transactions).

@@IDENTITY

@@IDENTITY is a global variable holding the last assigned identity value. Use it like this:

<code class="language-sql">INSERT INTO dbo.YourTable(columns....)
VALUES(..........)

SELECT @@IDENTITY</code>

However, @@IDENTITY reflects the last identity value across all scopes, potentially including earlier inserts within nested transactions. This might give incorrect results if multiple inserts occurred in the same session.

IDENT_CURRENT()

IDENT_CURRENT() provides another option. It retrieves the identity value for a specific table, regardless of scope or transaction:

<code class="language-sql">INSERT INTO dbo.YourTable(columns....)
VALUES(..........)

SELECT IDENT_CURRENT('dbo.YourTable')</code>

IDENT_CURRENT() guarantees the ID is from the specified table, even with multiple inserts within nested transactions.

Summary

Database developers need to understand these different methods for retrieving the last inserted row ID. Choosing the right method, based on your table structure and the context of your operations, ensures accurate ID retrieval and maintains data integrity.

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