How to For SQL Output Clause to Return a Column Not Being Inserted

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

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

Using OUTPUT clause to insert value not in INSERTED

Use MERGE instead of INSERT:

MERGE
INTO    trn_temp d
USING   (
        SELECT  D.DET_DATE, 'SOMETEXT' AS sometext, SUM(D.DET_NET) AS the_sum
        ...
        ) s
ON      (1 = 0)
WHEN NOT MATCHED THEN
INSERT  (TRN_TRAN_DATE, TRN_DESCRIPTION, TRN_AMT)
VALUES  (det_date, sometext, the_sum)
OUTPUT  s.*

Update:

To work around the GROUP BY problem, use this:

DECLARE @tmp TABLE
        (
        det_primary INT NOT NULL PRIMARY KEY
        )

MERGE
INTO    register r
USING   detail d
ON      (r.det_primary_link = d.det_primary)
WHEN NOT MATCHED THEN
INSERT  (det_primary_link, ins_date)
VALUES  (det_primary, GETDATE())
OUTPUT  d.det_primary
INTO    @tmp;

INSERT
INTO    trn_temp (trn_tran_date, trn_description, trn_amt)
OUTPUT  INSERTED.*
SELECT  det_date, 'sometext', SUM(det_net)
FROM    @tmp t
JOIN    detail d
ON      d.det_primary = t.det_primary
GROUP BY
        det_date

Insert into with output clause

If table 1 and table 2 have a 1:1 relationship, and no foreign key exists between the two then you could do this in a single statement:

MERGE Table1 AS a 
USING
(   SELECT  A.[group], A.account, B.title, B.amount, B.id2    
    FROM    Table1 AS A
            LEFT OUTER JOIN Table2 AS B
                ON A.id = B.id2
) AS b
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT ([group], account)
    VALUES (b.[group], b.account)
OUTPUT inserted.Id, B.title, B.amount 
    INTO Table2(id2, title, amount);

Example on SQL Fiddle

Realistically though, if your tables are related they should have a foreign key, and in most cases they won't be 1:1, rather 1:n.

In which case you would still need to use MERGE to caputre both the new ID and the old ID, but you would then need to capture this mapping in a temporary table before performing a second insert to Table2:

DECLARE @Map TABLE (OldID INT NOT NULL, NewID INT NOT NULL);

MERGE Table1 AS a 
USING
(   SELECT  A.ID, A.[group], A.account  
    FROM    Table1 AS A
) AS b
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT ([group], account)
    VALUES (b.[group], b.account)
OUTPUT inserted.Id, b.ID
    INTO @Map(NewID, OldID);

INSERT Table2 (id2, title, amount)
SELECT  m.NewID, b.title, b.amount
FROM    @Map AS m
        INNER JOIN Table2 AS b
            ON b.ID2 = m.OldID;

Example on SQL Fiddle

Return a source table column in an INSERT .. OUTPUT .. SELECT query

INSERT OUTPUT can only output columns from the table you're inserting into. MERGE can output source tables as well, eg

declare @keyMapping table(oldId int, [newId] int)

merge into T2 
using (select ID, X, Y, Z from T1) as src 
on 1=2
when not matched then insert (X,Y,Z) VALUES (src.X,src.Y,src.Z)
output inserted.ID, src.ID into @keyMapping(newId,oldId);

Output columns not in destination table?

Look at Example A:

DECLARE @temp TABLE (DocumentID int)

INSERT INTO FinishedDocument 
    OUTPUT INSERTED.DocumentID 
    INTO @temp
SELECT DocumentID 
FROM Document 
WHERE DocumentID = @DocumentID 

SELECT Document.DocumentId, Document.Description
FROM @temp AS t
INNER JOIN Document 
    ON t.DocumentID = Document.DocumentID

OUTPUT clause to SELECT a column is not working

The OUTPUT clause can only refer to the Inserted (and possibly Deleted) pseudo tables - change your statement to:

INSERT INTO B
OUTPUT INSERTED.ID, INSERTED.name INTO @Inserted
  SELECT id 
  FROM a 

Then it should work just fine.

---

Related Topics