Get the auto-generated key from a SQL insert operation

InsertIdentity.sql

SET NOCOUNT ON

  CREATE TABLE [Users]
  (
      [Id] INT PRIMARY KEY IDENTITY(1,1)
    , [Name] VARCHAR(50) NOT NULL
  )

GO

  DECLARE @key int;

-- insert one (retrieve generated key)
-- warning: this only works in the most simple scenario

  INSERT INTO [Users] ([Name]) 
    VALUES ('Jerry');
  SELECT @key = Id 
    FROM [Users] 
    WHERE [Name] = 'Jerry';
  PRINT @key;

-- insert another (key is abiguous)
-- warning: this scenario illustrates an ambiguous race condition

  INSERT INTO [Users] ([Name]) 
    VALUES ('Jerry');
  SELECT @key = Id 
    FROM [Users] 
    WHERE [Name] = 'Jerry';
  PRINT @key; 

-- insert another (output key with SCOPE_IDENTITY() - supports single record)
-- warning: SCOPE_IDENTITY() returns even if insert fails

    INSERT INTO [Users] ([Name]) 
      VALUES ('Jerry');
    SELECT @key = Id 
      FROM [Users] 
      WHERE [Name] = 'Jerry';
    PRINT SCOPE_IDENTITY();

-- insert another (output key with OUTPUT CLAUSE - supports multiple records)
-- note: this is the GOLDEN approach, works every single time

    DECLARE @keys TABLE (Id int);
    INSERT INTO [Users] ([Name]) 
      OUTPUT inserted.Id INTO @keys 
      VALUES ('Jerry');
    SELECT @key = Id FROM @keys;
    PRINT @key;

GO

    DROP TABLE [Users]

Output looks like this: