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 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?

Chiropractor

You can return any of the columns of the table you're inserted a row into, in your OUTPUT clause. So even if you don't provide a value for a given column in your INSERT statement, you can still specify that column in the OUTPUT clause. You can however not return SQL variables or columns from other tables. – December

@December thanks for your reply, but I don't have the field I need in the destination table (I need PracticeId, which is not on ReportOption) – Chiropractor

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.

Ecesis

Thanks, this does the trick! I was going to use a fake temp field but this is much more elegant. – Chiropractor

Excellent! This trick is a grain of gold! Added to the first row in the collection! – Synchromesh

Suweet! I wish it didn't have to use the occasionally buggy MERGE command, but it's perfectly elegant otherwise. – Pollard

Ha! I just re-invented this solution before I saw this thread (and having never previously used or even heard of Merge Statement). I was trying to Output my Insert's to a mapping Table that included Identity value from target Table with a non-Insert'ed value from the Select - From Table. Without this solution, I imagine I'd have to: a) start Transaction, b) get next Identity, c) Insert into temp Table with computed Identity, d) set Identity_Insert, e) Insert into target Table from temp Table, f) clear Identity_Insert. – Directly

Be warned. I used a merge statement that over the past year has grown with usage. We started having timeouts during saves and it turned out that because the merge statement always locks the tables we were having 35-160 seconds of table locking every 4 minutes. I'm having to reconstruct several merge statements to use insert/updates and limiting the number of rows they update to 500 per insert/update to avoid table locking. I estimate that this very important table was being kept locked nearly 2 1/2 hours per day which was causing everything from slow saves to timeouts. – Segura

Link needs a login. Is it this one? dataeducation.com/… – Rhines

@AjVJsy Yeah, that looks like it. I have updated the link in the answer. Cheers – Ecesis

My problem with MERGE statement instead off INSERT is, that I cannot freely use NEXT VALUE FOR statement in MERGE. That's why I prefer INSERT in some cases. Sad, that OUTPUT in an INSERT ... SELECT ... FROM statement cannot access fields from the FROM clause. – Skeptic

Also, a deal breaker for many, is that MERGE has a whole lot of unfixed bugs in it, which surface under weird conditions. E.g. see this article by Aaron Bertrand. Microsoft is refusing to fix some of them, and my secret suspicion is that MS deprecated their whole MS Connect service just to try and make us forget about all those bugs in the MERGE statement they don't want to fix. – Iou

Example 'E' in learn.microsoft.com/en-us/sql/t-sql/queries/… suggests you can reference other columns ... although when I try it doesn't work and I need this MERGE trick. Is this something that should work nowadays in SQL 2019? – Washout

@Washout Unfortunately not., even in SQL Server 2012 unused columns could be used in the OUTPUT for deletes and updates, just not inserts, and it appears in 2019 there is no advance on that position. It is a bit odd, however I suspect there's a good reason. I am sure if it piqued your interest enough, you could ask on dba.stackexchange.com and someone who truly understands what goes on under the hood would be able to answer the question as to why this design decision was made. Unfortunately I have no clue – Ecesis

@Ecesis - thanks! I hadn't noticed that this problem specifically related to INSERT statements, although re-reading the docs I see they do say from_table_name can be used in DELETE, UPDATE, or MERGE statements ... i.e. not INSERT. Of course it's an INSERT statement I'm interested in. – Washout

Answer 2

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

---

MERGE will only work for SQL Server 2008 or higher.

For the rest, I found another workaround which will give you the 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

The main trick is that we are filling the mapping table twice with ordered data from the source and destination table.

For more details, see Merging Inserted Data Using OUTPUT in SQL Server 2005.

Cameo

This wouldn't solve my prob. I need to Output my Insert's to an output Table that includes an Identity value from the target Table with a non-Insert-ed value (the PK) from the source Table (btw, so I could then (in a different Batch) use that output Table to populate a Column in the source Table with the Identity value). Without a Merge, I imagine I'd have to: a) start Transaction, b) get next Identity, c) Insert into temp Table with computed Identity, d) set Identity_Insert, e) Insert into target Table from temp Table, f) clear Identity_Insert. – Directly