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

Accessing Newly Generated Identity Values with the OUTPUT Clause

Inserting data into tables containing identity columns often requires retrieving the newly generated ID. The OUTPUT clause provides an efficient way to accomplish this, eliminating the need for extra queries.

Approach 1: OUTPUT to a Table Variable

This method directs the identity value to a declared 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>

The @OutputTbl variable now holds the generated ID.

Approach 2: OUTPUT to a Non-Table Variable

Certain database systems (like SQL Server) support outputting directly to non-table variables. Note that this isn't universally supported; PostgreSQL, for instance, uses RETURNING instead.

SQL Server Example:

<code class="language-sql">DECLARE @ID INT;

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

The ID is stored in @ID.

PostgreSQL Example:

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

Here, @ID receives the returned ID. The RETURNING clause functions similarly to OUTPUT in other systems. Choose the appropriate method based on your specific database system.

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