Using OUTPUT with joined tables

Question

Why doesn't the following work?

INSERT INTO dbo.Pending_Break 
  (Pending_Pod_ID, Break_Date_Time, Break_Length, Booked_Length)            
OUTPUT INSERTED.Pending_BH_ID -- This is the inserted identity
     , INSERTED.Pending_Pod_ID
     , INSERTED.Break_Name
     , INSERTED.Break_Date_Time
     , pb.PENDING_BH_ID  -- complains on this one
  INTO #InsertedPendingBreaks
 SELECT ippod.Pending_Pod_ID,  
        pb.Break_Date_Time
        pb.break_length,  
        0
   FROM PendingBreak pb
   JOIN InsertedPod ippod ON ...

Can I not use anything other than Inserted or Deleted in the OUTPUT clause?

`INSERTED` and `DELETED` only, `UPDATED` doesn't exist in the [TSQL OUTPUT clause](http://msdn.microsoft.com/en-us/library/ms177564.aspx). You've got identically named columns, which would require a column alias to tell them apart... — OMG Ponies, Sep 27 '11 at 01:10

score 3 · Answer 1 · edited May 23 '17 at 12:15

Can I not use anything other than Inserted or Deleted in the OUTPUT clause?

No you can't. At least not with an insert. In SQL Server 2008 you can convert your insert to a merge statement instead and there you can use values from the source table in the output clause.

Have a look at this question how to do that in SQL Server 2008. Using merge..output to get mapping between source.id and target.id

score 0 · Accepted Answer · answered Sep 27 '11 at 01:17

0

The inserted and deleted tables are available only in DML triggers. I'm not sure if you just pulled a code snippet out of a trigger, but if that is a standalone batch then it won't work.

Also, there is no updated table. An update is a delete and then an insert for this. deleted contains the old data and inserted contains the new data on an UPDATE.

answered Sep 27 '11 at 01:17

2

Not only in triggers. Also in the output clause. http://msdn.microsoft.com/en-us/library/ms177564.aspx – Mikael Eriksson Sep 27 '11 at 05:34

Using OUTPUT with joined tables

2 Answers2