sql - join/subquery in OUTPUT clause -

i'm updating table , adding data on changes using tsql output clause. problem updated table contains normalised ids data want output , need in human readable form. here's simplified example show mean

create table location (locationid int, name varchar(50))  create table user (userid int, username varchar(50), locationid int)  --insert test data  update user set locationid = @somevalue userid = @someothervalue 

i know can add like

output inserted.username + ' location changed ' + deleted.locationid + ' ' + inserted.locationid 

but that's no use end user wants know location name, not database id. tried replacing deleted.locationid (select name location locationid = deleted.locationid), , inserted.locationid , met error subqueries not allowed in output clause

i've seen couple of answers saying joins allowed in output clauses, haven't found way join separately on inserted , deleted. possible in single step in sql server 2008 r2, or need via intermediate step of storing old , new values joining in second query output need?

following microsoft article output describes limitations of output clause in dml operations insert, update , delete.

it describes can't use subquery output clause

so here can records locationid inserted , deleted row , in table variable , can further location name using join location table

example

insert location values(1,'delhi') insert location values(2,'noida')   insert [user] values(1,'sandeep',1)  declare  @result table(username varchar(50), oldlocationid int,newlocaionid int)   update [user] set locationid=2 output  inserted.username, deleted.locationid,inserted.locationid @result userid=1  select * @result