How to Retrieve Automatically Generated Keys in SQL Server?

Accessing Auto-Generated Keys in SQL Server

When working with SQL Server tables containing auto-generated ID columns, efficiently retrieving the newly generated values is crucial. This guide demonstrates a simple and effective method using SQL Server's built-in capabilities.

The Solution:

The INSERT ... OUTPUT statement provides a concise way to insert a row and simultaneously capture the generated key(s) within a single operation. Here's how it works:

<code class="language-sql">INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES ('bob');</code>

In this example, table represents your table with an ID column configured as an identity column or using a data type supporting automatic value generation (like GUID). The OUTPUT clause directs the newly inserted row's columns, including the generated ID, to a temporary result set.

The Inserted alias provides access to this temporary result set, allowing retrieval of the ID using Inserted.ID.

Practical Application:

To store the generated ID in a variable, use the following:

<code class="language-sql">DECLARE @id INT;
INSERT INTO table (name)
OUTPUT @id = Inserted.ID
VALUES ('bob');
SELECT @id;</code>

This approach isn't limited to identity columns; it also works with other auto-generated columns, such as GUIDs. For example:

<code class="language-sql">INSERT INTO table (guid)
OUTPUT Inserted.guid
VALUES (NEWID());</code>

This inserts a row with a GUID column and immediately retrieves the generated GUID value.

The above is the detailed content of How to Retrieve Automatically Generated Keys in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!