How Can I Retrieve Identity Values After an INSERT Statement Using SQL Server's OUTPUT Clause?

Retrieving Newly Generated Identity Values with SQL Server's OUTPUT Clause

When inserting data into a SQL Server database, often you need the newly generated identity value for the inserted row. The OUTPUT clause provides a clean and efficient way to accomplish this. This article explores two primary methods.

Method 1: Output to Console or Using ExecuteScalar()

For quick debugging or retrieving the identity value in a non-T-SQL context (like a .NET application), the OUTPUT clause can send the value directly to the SSMS console or be captured using ExecuteScalar().

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

In .NET:

<code class="language-csharp">int newId = command.ExecuteScalar();</code>

Method 2: Output to Table Variable or Temporary Table

For scenarios requiring the identity value within a T-SQL stored procedure or batch, a table variable or temporary table offers a powerful solution. The OUTPUT clause directs the inserted identity value into this table, enabling further processing within the T-SQL environment.

<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')

-- Now you can use the ID from @OutputTbl
SELECT ID FROM @OutputTbl;</code>

These techniques empower developers to seamlessly retrieve generated identity values from INSERT statements, facilitating debugging, returning values to applications, and enabling complex T-SQL workflows.

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