How Can SQL Server's OUTPUT Clause Retrieve Auto-Generated Insert Values?

Accessing Auto-Generated Keys Post-INSERT in SQL Server

Often, you need the value of an automatically generated key after an INSERT operation. SQL Server's OUTPUT clause offers a streamlined method for achieving this, particularly useful from SQL Server 2008 onwards.

Illustrative Example:

Let's say we have a table called "person" with columns "id" (auto-generated) and "name". To add a record with the name "bob" and obtain the newly generated ID, use this command:

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

Breakdown:

• The OUTPUT clause directs the return of a specified column's value—in this instance, "id"—from the newly inserted row.
• Inserted acts as a pseudo-table representing the just-inserted row.
• Inserted.id explicitly tells SQL Server to fetch the "id" value from the inserted row.

Further Considerations:

• The OUTPUT clause is capable of retrieving multiple columns; just list the desired columns, comma-separated.
• Its functionality extends beyond IDENTITY columns to encompass other automatically generated columns like GUIDs.
• Support for the OUTPUT clause begins with SQL Server 2005.

The above is the detailed content of How Can SQL Server's OUTPUT Clause Retrieve Auto-Generated Insert Values?. For more information, please follow other related articles on the PHP Chinese website!