How to get value from 'from' table in OUTPUT part of the query

Question

This is my query:

INSERT INTO Translated
OUTPUT @ChangeID,Inserted.ResourceID,Project,Inserted.TranslatedValue into backup
Select NEWID(),ResourceID,TranslatedValue,getdate()
from Resources 
where Project ='blahblah'

Right now I cant get the value of Project in OUTPUT part because it's from Resources table but not inserted, is there any easy way to get the value without modifying the structure of the query?

Possible duplicate of [Is it possible to for SQL Output clause to return a column not being inserted?](http://stackoverflow.com/q/10949730/1048425) — GarethD, Aug 18 '14 at 15:14
I have refrained from officially marking this as a duplicate since I am unsure exactly what you mean by *"without modifying the structure of the query"* - Is changing the query to use `MERGE` rather than `INSERT` more of a modification than you wanted to make? Or does it solve your problem? — GarethD, Aug 18 '14 at 15:58
@GarethD,since the actual query is huge and messy I'd rather not to use Merge instead Insert, the answers below solved my problem and I'm going to accept one of those if there's no more new answers. — SSilicon, Aug 18 '14 at 16:03

score 1 · Accepted Answer · answered Aug 18 '14 at 15:14

1

The output clause can only refer to the inserted row. You could update the backup table after the query.

update  b
set     project = r.projcet
from    backup b
join    resources r
on      b.ResourceID = r.ResourceID
where   b.project is null

answered Aug 18 '14 at 15:14

Andomar

232,371
49
380
404

score 1 · Answer 2 · answered Aug 18 '14 at 15:14

You would have to perform a second query that joins the OUTPUT table to Resources to get the Project value for each row inserted.

Maybe something like the following:

Select Project
From   Backup B
Inner Join Resources R on B.ResourceID = R.ResourceID
where B.Project is null

How to get value from 'from' table in OUTPUT part of the query

2 Answers2