How Can I Retrieve the Last Inserted Row ID Using SQL?

Accessing the ID of the Newly Added Record in SQL

Frequently, database tasks require retrieving the ID of the most recently inserted row. This is especially important when a new entry is added and that ID is needed for further operations.

Consider a table with columns for ID, first name, and last name. After an INSERT statement, the goal is to obtain the newly generated ID (e.g., 4).

SQL offers a simple solution for this common problem. If your SQL Server table utilizes an INT IDENTITY (or BIGINT IDENTITY) column, the following SQL command will suffice:

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

SELECT SCOPE_IDENTITY()</code>

SCOPE_IDENTITY() returns the last identity value generated within the current scope. It's vital to remember that no other INSERT statements should execute between inserting the row and retrieving the identity value using this method.

Other methods, such as @@IDENTITY and IDENT_CURRENT, provide alternative approaches. For a detailed comparison and potential limitations of these options, please consult Pinal Dave's blog.

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