Using OUTPUT to return both the newly Inserted Id and the original Id from a table during an INSERT [duplicate]

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 filling 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 from the destination table in 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 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.