How to Retrieve Identity Values After an INSERT in SQL Server?

Retrieving Newly Generated Identity Values in SQL Server

Problem: How can we obtain the automatically generated key value (like an 'id' from an identity column) following an INSERT operation in SQL Server 2008?

Solution: SQL Server offers a straightforward method using the OUTPUT clause:

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

The OUTPUT clause directs the return of specified columns after the INSERT completes. Here, we select the ID column from the Inserted pseudo-table, which holds the data of the newly inserted row.

Explanation:

When inserting data into a table with an identity column, the database assigns a new, unique value. The OUTPUT clause efficiently retrieves this generated value simultaneously with the INSERT, avoiding the need for a separate SELECT statement.

Important Note: The OUTPUT clause isn't limited to identity columns; it can also retrieve other non-identity columns (including GUIDs).

The above is the detailed content of How to Retrieve Identity Values After an INSERT in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!