How to Retrieve a Recently Inserted GUID Primary Key in SQL Server?

Accessing the Newly Inserted GUID Primary Key in SQL Server

SQL Server's SCOPE_IDENTITY() function retrieves the last inserted identity value, but this is unsuitable for GUID primary keys. The OUTPUT clause provides a robust solution for capturing the newly generated GUID.

Here's how to obtain the recently inserted GUID:

1. Table Creation (GUID Primary Key):

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

2. Insertion with GUID Capture using OUTPUT:

   <code class="language-sql">DECLARE @op TABLE (
       ColGuid uniqueidentifier
   )
   
   INSERT INTO dbo.GuidPk (Col2)
   OUTPUT inserted.ColGuid INTO @op
   VALUES (1)</code>

   This inserts a record, letting SQL Server generate the GUID. The OUTPUT clause redirects the generated ColGuid to the @op table variable.

3. Verification:

   <code class="language-sql">SELECT * FROM @op;  -- Shows the newly inserted GUID
   
   SELECT * FROM dbo.GuidPk; -- Confirms the record in the table</code>

The OUTPUT clause efficiently retrieves the inserted GUID, even with multiple simultaneous insertions. This method ensures accurate retrieval of the newly generated primary key.

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