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().

INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')

In .NET:

int newId = command.ExecuteScalar();

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.

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;

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!