How Can I Retrieve an Auto-Generated Key After an INSERT in SQL Server?

Accessing Auto-Generated Keys Following an SQL Server INSERT

Problem: You need to obtain the automatically generated key value after adding a new row to an SQL Server table.

Solution:

While previously this involved a separate SELECT query, SQL Server 2008 and later versions offer a more efficient method using the OUTPUT clause.

Here's the syntax:

<code class="language-sql">INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');</code>

This single statement inserts the new record and simultaneously returns the newly generated ID from the Inserted pseudo-table. This ID is then immediately accessible for use within the same batch.

Important Considerations:

• This technique is effective for both IDENTITY columns and non-IDENTITY columns (like GUIDs).
• Multiple columns can be specified in the OUTPUT clause, allowing retrieval of additional information from the newly inserted record.

The above is the detailed content of How Can I Retrieve an Auto-Generated Key After an INSERT in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!