How to Retrieve a Newly Inserted GUID Primary Key in SQL Server Without Variables?

Retrieving the Newly Inserted GUID Primary Key in SQL Server without Variables

Working with GUIDs as primary keys often requires retrieving the newly inserted GUID without using variables. This article demonstrates how to achieve this efficiently.

Why SCOPE_IDENTITY() Doesn't Work for GUIDs

SCOPE_IDENTITY() is unsuitable for retrieving GUIDs because it only functions with integer-based identity columns.

The Solution: The OUTPUT Clause

The OUTPUT clause provides a clean and efficient solution, even when inserting multiple rows.

<code class="language-sql">CREATE TABLE dbo.GuidPk (
    ColGuid uniqueidentifier NOT NULL DEFAULT NewSequentialID(),
    Col2    int              NOT NULL
);

DECLARE @op TABLE (
    ColGuid uniqueidentifier
);

INSERT INTO dbo.GuidPk (Col2)
OUTPUT inserted.ColGuid INTO @op
VALUES (1);

SELECT * FROM @op;

SELECT * FROM dbo.GuidPk;</code>

The OUTPUT clause redirects the inserted ColGuid value into the temporary table @op. Subsequently, querying @op directly retrieves the newly inserted GUID. This method is reliable and avoids the need for variables.

Further Reading:

• Understanding the SQL Server OUTPUT Clause (This link is slightly modified for better SEO and clarity)

The above is the detailed content of How to Retrieve a Newly Inserted GUID Primary Key in SQL Server Without Variables?. For more information, please follow other related articles on the PHP Chinese website!