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

Question

I've made some modifications to my database and I need to migrate the old data to the new tables. For that, I need to fill a table (ReportOptions) taking the data from the original table (Practice) and fill a second intermediate table (PracticeReportOption).

ReportOption (ReportOptionId int PK, field1, field2...)

Practice (PracticeId int PK, field1, field2...)

PracticeReportOption (PracticeReportOptionId int PK, PracticeId int FK, ReportOptionId int FK, field1, field2...)

I made a query to get all the data I need to move from Practice to ReportOptions, but I'm having trouble to fill the intermediate table

--Auxiliary tables

DECLARE @ReportOption TABLE (PracticeId int /*This field is not on the actual ReportOption table*/, field1, field2...)

DECLARE @PracticeReportOption TABLE (PracticeId int, ReportOptionId int, field1, field2)

--First I get all the data I need to move

INSERT INTO @ReportOption

SELECT P.practiceId, field1, field2...

  FROM Practice P

--I insert it into the new table, but somehow I need to have the repation PracticeId / ReportOptionId

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

--This would insert the relationship, If I knew how to get it!

INSERT INTO @PracticeReportOption (PracticeId, ReportOptionId)

SELECT PracticeId, ReportOptionId

  FROM @ReportOption

If I could reference a field that is not on the destination table on the OUTPUT clause, that would be great (I think I can't, but I don't know for sure). Any ideas on how to accomplish my need?

Answer 1

You can replace this by using MERGE instead of insert:

Code1:

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 Code 2:

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);

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

To test the code try using this:

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 

For more information, you can refer to http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge/