Is it possible to for SQL Output clause to return a column not being inserted?

Question

You can do this by using MERGE instead of insert:

so replace this

INSERT INTO ReportOption (field1, field2...)
OUTPUT @ReportOption.PracticeId, --> this is the field I don't know how to get
       inserted.ReportOptionId
  INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT field1, field2
  FROM @ReportOption

with

MERGE INTO ReportOption USING @ReportOption AS temp ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (field1, field2)
    VALUES (temp.Field1, temp.Field2)
    OUTPUT temp.PracticeId, inserted.ReportOptionId, inserted.Field1, inserted.Field2
    INTO @PracticeReportOption (PracticeId, ReportOptionId, Field1, Field2);

The key is to use a predicate that will never be true (1 = 0) in the merge search condition, so you will always perform the insert, but have access to fields in both the source and destination tables.

---

Here is the entire code I used to test it:

CREATE TABLE ReportOption (ReportOptionID INT IDENTITY(1, 1), Field1 INT, Field2 INT)
CREATE TABLE Practice (PracticeID INT IDENTITY(1, 1), Field1 INT, Field2 INT)
CREATE TABLE PracticeReportOption (PracticeReportOptionID INT IDENTITY(1, 1), PracticeID INT, ReportOptionID INT, Field1 INT, Field2 INT)

INSERT INTO Practice VALUES (1, 1), (2, 2), (3, 3), (4, 4)


MERGE INTO ReportOption r USING Practice p ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (field1, field2)
    VALUES (p.Field1, p.Field2)
    OUTPUT p.PracticeId, inserted.ReportOptionId, inserted.Field1, inserted.Field2
    INTO PracticeReportOption (PracticeId, ReportOptionId, Field1, Field2);

SELECT  *
FROM    PracticeReportOption

DROP TABLE ReportOption
DROP TABLE Practice
DROP TABLE PracticeReportOption 

---

More reading, and the source of all that I know on the subject is Here

Maybe someone who uses MS SQL Server 2005 or lower will find this answer useful.

---

MERGE will work only for SQL Server 2008 or higher. For rest I found another workaround which will give you ability to create kind of mapping tables.

Here's how Resolution will look like for SQL 2005:

DECLARE @ReportOption TABLE (ReportOptionID INT IDENTITY(1, 1), Field1 INT, Field2 INT)
DECLARE @Practice TABLE(PracticeID INT IDENTITY(1, 1), Field1 INT, Field2 INT)
DECLARE @PracticeReportOption TABLE(PracticeReportOptionID INT IDENTITY(1, 1), PracticeID INT, ReportOptionID INT, Field1 INT, Field2 INT)

INSERT INTO @Practice (Field1, Field2) VALUES (1, 1)
INSERT INTO @Practice (Field1, Field2) VALUES (2, 2)
INSERT INTO @Practice (Field1, Field2) VALUES (3, 3)
INSERT INTO @Practice (Field1, Field2) VALUES (4, 4)

INSERT INTO @ReportOption (field1, field2)
    OUTPUT INSERTED.ReportOptionID, INSERTED.Field1, INSERTED.Field2 INTO @PracticeReportOption (ReportOptionID, Field1, Field2)
    SELECT Field1, Field2 FROM @Practice ORDER BY PracticeID ASC;


WITH CTE AS ( SELECT PracticeID, ROW_NUMBER() OVER ( ORDER BY PracticeID ASC ) AS ROW FROM @Practice )
UPDATE M SET M.PracticeID = S.PracticeID 
    FROM @PracticeReportOption AS M
    JOIN CTE AS S ON S.ROW = M.PracticeReportOptionID

    SELECT * FROM @PracticeReportOption

Main trick is that we are filling mapping table twice with ordered data from Source and destination table. For More details here: Merging Inserted Data Using OUTPUT in SQL Server 2005