How to OUTPUT columns that you did not INSERT in T-SQL

The OUTPUT clause is a very useful tool in Microsoft SQL, allowing for getting automatically inserted columns in the same command as the INSERT. Imagine you have a table with an identity column and you need the generated ids as you insert new records. It would look like this:

CREATE TABLE MyTable 
  ( 
     Id    INT PRIMARY KEY IDENTITY(1, 1), 
     Value NVARCHAR(100) 
  ) 

CREATE TABLE AnotherTable 
  ( 
     Value               NVARCHAR(100), 
     AnotherValue        NVARCHAR(100), 
     SomeConditionIsTrue BIT 
  ) 

go 

CREATE TABLE #ids 
  ( 
     Id INT ,
     AnotherValue        NVARCHAR(100)
  ) 

INSERT INTO MyTable (Value) 
    OUTPUT inserted.Id INTO #ids (id) 
SELECT Value 
    FROM   AnotherTable 
WHERE  SomeConditionIsTrue = 1 

-- Do something with the inserted Ids 

However, what do you do if you want to also insert the column AnotherValue to the #ids table? Something like this does not work:

INSERT INTO MyTable (Value) 
    OUTPUT inserted.Id,AnotherTable.AnotherValue INTO #ids (id,AnotherValue) 
SELECT Value 
    FROM   AnotherTable 
WHERE  SomeConditionIsTrue = 1 

Enter the often ignored MERGE, which can help us translate the query above into:

MERGE INTO MyTable USING (
    SELECT Value , AnotherValue
        FROM   AnotherTable
    WHERE  SomeConditionIsTrue = 1 
) t ON 1=0 --FALSE
WHEN NOT MATCHED THEN
  INSERT (Value) VALUES (t.Value)
  OUTPUT Inserted.Id, t.AnotherValue INTO #ids (Id, AnotherValue);

Note the 1=0 condition so that the merge never "matches" and how the select from the first query now contains all the columns needed to be output, even if only some of them are inserted in the insert table.

This post was prompted by a StackOverflow answer that, as great as it was, didn't make it clear what to do when you get your values from a more complicated select. The answer is simple: put it all in the 'using' table.