How to Retrieve the Auto-Generated ID After an INSERT in SQL Server?

Retrieving Auto-Generated IDs After SQL Server INSERT Operations

Adding new data to a SQL Server table often involves auto-generated identity columns. This guide demonstrates how to efficiently retrieve the newly generated ID after an INSERT statement. SQL Server's OUTPUT clause provides a clean solution.

The OUTPUT Clause Solution

The OUTPUT clause lets you specify columns from the newly inserted row to be returned. This is ideal for capturing the auto-generated ID.

Syntax:

<code class="language-sql">INSERT INTO YourTable (column1, column2, ...)
OUTPUT Inserted.IDColumn  -- Replace IDColumn with your actual ID column name
VALUES (value1, value2, ...);</code>

Explanation:

• Inserted: A temporary table containing the newly inserted row. It mirrors the structure of your target table.
• IDColumn: The name of your auto-generated identity column. Replace this placeholder with your column's actual name.

Illustrative Example:

Let's say you have a Customers table:

<code class="language-sql">CREATE TABLE Customers (
  CustomerID INT IDENTITY(1, 1) PRIMARY KEY,
  CustomerName VARCHAR(255) NOT NULL
);</code>

To insert a new customer and get the CustomerID, use:

<code class="language-sql">INSERT INTO Customers (CustomerName)
OUTPUT Inserted.CustomerID
VALUES ('Acme Corp');</code>

This returns the newly generated CustomerID. You can then use this value in further queries or store it in a variable.

Important Note: The OUTPUT clause isn't limited to a single column. You can retrieve multiple columns from the inserted row by listing them, comma-separated, within the OUTPUT clause.

The above is the detailed content of How to Retrieve the Auto-Generated ID After an INSERT in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!