How to get result from Update statement ?

Question

How to get result of Update statement in output statement

I work on sql server 2012

update r set r.PartsLCUpdated=1,r.PartsLCDate=getdate(), r.IsValid=g.IsValid, r.HasReplacementCode=g.NewHasReplacementCode, r.JobUpdateHasreplacement=g.JobUpdateHasreplacement
output Inserted.PartsLCUpdated,Inserted.PartsLCDate,Inserted.IsValid,Inserted.HasReplacementCode,Inserted.JobUpdateHasreplacement into #getfinal
from replaceTest2 r with(nolock)
inner join #GetDataElastic g on g.PartIDC=r.partidc and g.PartIDX=r.partidx and g.OldHasReplacementCode=r.HasReplacementCode and g.IsValid=r.IsValid

I need to get result of update statement on output
so How to do that please ?

Answer 1

Hi @ahmed salah ,

You could declare a table variable called @getfinal to store the results of the OUTPUT clause into this table variable. Then you could use the OUTPUT INTO syntax in order to store the results into a table variable.

Please refer below:

DECLARE @getfinal table( PartsLCUpdated int,  
                        PartsLCDate datetime,  
                        IsValid varchar(50),  
                        HasReplacementCode varchar(50),  
                        JobUpdateHasreplacement varchar(50));  
  
update r   
set r.PartsLCUpdated=1,r.PartsLCDate=getdate(),r.IsValid=g.IsValid, r.HasReplacementCode=g.NewHasReplacementCode, r.JobUpdateHasreplacement=g.JobUpdateHasreplacement  
output   
Inserted.PartsLCUpdated,Inserted.PartsLCDate,Inserted.IsValid,Inserted.HasReplacementCode,Inserted.JobUpdateHasreplacement   
into @getfinal  
from replaceTest2 r with(nolock)  
inner join #GetDataElastic g on g.PartIDC=r.partidc and g.PartIDX=r.partidx and g.OldHasReplacementCode=r.HasReplacementCode and g.IsValid=r.IsValid  
  
Select * from @getfinal  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

DECLARE @tbl TABLE (
    Id int
);
INSERT INTO @tbl VALUES (1), (10);

UPDATE @tbl 
SET Id = Id * 2
OUTPUT inserted.Id;

You can insert the output into a table variable.