title: Using OUTPUT after INSERT to get value of identity column into a (non-table value) variable
body: <p>Given the following simple test table:</p>
  
  <pre><code>CREATE TABLE dbo.Test
  (
    Id bigint IDENTITY(1,1) NOT NULL,
    Name varchar(50) NULL
  )
  </code></pre>
  
  <p>I would like to get the value of the identity column into a scalar variable after the <code>INSERT</code> using the <code>OUTPUT</code> clause, but this does not work:</p>
  
  <pre><code>DECLARE @InsertedId BIGINT;
  
  INSERT INTO Test(Name) 
    OUTPUT @InsertedId=inserted.Id
    VALUES ('Michael')
  
  -- Display resulting id for debugging
  SELECT @InsertedId;
  -- ...
  </code></pre>
  
  <p>I know I can easily do this using <code>SCOPE_IDENTITY()</code> after the <code>INSERT</code>, but is it possible to do it as part of the <code>INSERT</code> statement using the <code>OUTPUT</code> clause without resorting to a table variable?</p>
  
  <p><strong>Update</strong>, another contrived attempt that is also not legal:</p>
  
  <pre><code>-- Return the id as a result set
  INSERT INTO Test(Name) 
  OUTPUT inserted.Id AS TheId
  VALUES ('Michael')
  
  -- But you can't use the result set as a derived table...
  SELECT TheId FROM
  (
    INSERT INTO Test(Name) 
    OUTPUT inserted.Id AS TheId
    VALUES ('Michael')
  )
  
  -- ..., or you would be able to do this
  SELECT TOP 1 @InsertedId=TheId
  FROM
  (
    INSERT INTO Test(Name) 
    OUTPUT inserted.Id AS TheId
    VALUES ('Michael')
  )
  </code></pre>