How Can I Retrieve the Identity Value After an INSERT Statement in SQL?

Retrieving Newly Generated Identity Values After SQL INSERT Statements

Often, after inserting data into a SQL table with an auto-incrementing identity column, you need to access the newly generated ID. The OUTPUT clause offers a convenient way to achieve this.

This guide illustrates two common methods for retrieving identity values using the OUTPUT clause:

Method 1: Output to the Console (and then to your application)

The simplest method outputs the identity value directly to the console. This is useful when dealing with single inserts and retrieving the ID in your application code:

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

Your application code (e.g., using ADO.NET's ExecuteScalar()) can then capture this single output value.

Method 2: Output to a Table Variable (for multiple inserts or further processing)

For multiple inserts or when you need to process the generated IDs within your T-SQL code, use a table variable as the target of the OUTPUT clause:

<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 access the ID(s) from @OutputTbl
SELECT ID FROM @OutputTbl;</code>

This method allows you to work with multiple inserted IDs directly within your stored procedure or T-SQL script. You can also use a temporary table (#temp) or even a permanent table as the output target.

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