How Can I Retrieve Identity Values After an INSERT Statement Using the OUTPUT Clause?

Efficiently Retrieving Newly Inserted Row IDs with SQL's OUTPUT Clause

Database operations frequently require retrieving the ID of a newly inserted row within the same query. This article demonstrates how to use the powerful OUTPUT clause to accomplish this in SQL.

SQL Query Structure

The OUTPUT clause is seamlessly integrated into the INSERT statement:

<code class="language-sql">INSERT INTO MyTable (Name, Address, PhoneNo)
OUTPUT INSERTED.Id AS @var  --Retrieves the ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333');</code>

Displaying the ID

The simplest method displays the newly generated ID directly in the query results:

<code class="language-sql">INSERT INTO MyTable (Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333');</code>

Storing the ID in a Variable

For more complex scenarios requiring further processing within T-SQL, use a table variable:

<code class="language-sql">DECLARE @OutputTbl TABLE (ID INT);

INSERT INTO MyTable (Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl (ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333');</code>

This approach stores the generated ID in @OutputTbl, enabling subsequent operations within your T-SQL code. The OUTPUT clause provides flexibility to include other relevant column data as needed.

The above is the detailed content of How Can I Retrieve Identity Values After an INSERT Statement Using the OUTPUT Clause?. For more information, please follow other related articles on the PHP Chinese website!