How to store inserted/updated data using output clause when merge data from xml

Question

i want to insert and update data into table from xml. so i can use merge statement but i want to store the data into temp table when inserted or updated into table. so i think i have to use output clause. hence i do not know how to store inserted or updated data into tmp table using output clause.

Declare @Data xml  

set @Data=  
'<Root>  
<Student>  
<Name>Rakesh</Name>  
<Marks>80</Marks>  
</Student>  
<Student>  
<Name>Mahesh</Name>  
<Marks>90</Marks>  
</Student>  
<Student>  
<Name>Gowtham</Name>  
<Marks>60</Marks>  
</Student>  
</Root>'  


Merge into Student as Trg  
Using (select d.x.value('Name[1]','varchar(20)') as Name ,  
d.x.value('Marks[1]','int') as Marks from   
@data.nodes('/Root/Student')as d(x)) as Src  
on Trg.Name=Src.Name  
When Matched Then update set   
Trg.Marks=Src.Marks  
when not matched by target then   
insert (Name,Marks) values(Src.Name,Src.Marks);  

here inserting or updating data into Student table. so am looking for a way to stored each inserted/ updated data into temp table using output clause. so please share a example. thanks

Answer 1

Hi @Sudip Bhatt ,

Please refer below and check whether it is helpful.

drop table if exists Student  
  
Declare @Data xml    
        
 set @Data=    
 '<Root>    
 <Student>    
 <Name>Rakesh</Name>    
 <Marks>80</Marks>    
 </Student>    
 <Student>    
 <Name>Mahesh</Name>    
 <Marks>90</Marks>    
 </Student>    
 <Student>    
 <Name>Gowtham</Name>    
 <Marks>60</Marks>    
 </Student>    
 </Root>'    
   
  create table Student (  
  Name varchar(10),  
  Marks int  
  )  
  
  insert into Student values  
  ('Rakesh',90),  
  ('Mahesh',80),  
  ('Jack',80)  
  
DECLARE @archive TABLE  
(  
ActionType  varchar(10),  
  Name varchar(10),  
  Marks int  
);  
  
 Merge into Student as Trg    
 Using (select d.x.value('Name[1]','varchar(20)') as Name ,    
 d.x.value('Marks[1]','int') as Marks from     
 @data.nodes('/Root/Student')as d(x)) as Src    
 on Trg.Name=Src.Name    
 When Matched Then update set     
 Trg.Marks=Src.Marks    
 when not matched  then     
 insert (Name,Marks) values (Src.Name,Src.Marks)  
 OUTPUT  
   $action ,  
   inserted.*  
INTO @archive;  
  
 select * from Student  
--Name Marks  
--Rakesh 80  
--Mahesh 90  
--Gowtham 60  
  
 select * from @archive   
 WHERE  ActionType IN ( 'INSERT', 'UPDATE' );  
--ActionType Name Marks  
--INSERT Gowtham 60  
--UPDATE Rakesh 80  
--UPDATE Mahesh 90  

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.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

Here is a conceptual example how to do it.
This line does what you are looking for: INTO #log (a, dt, what); You just need to add columns that you interested in.

-- DDL and sample data population, start
--Create a target table
DECLARE @Products TABLE (
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate MONEY
);

INSERT INTO @Products
VALUES (1, 'Tea', 10.00),
   (2, 'Coffee', 20.00),
   (3, 'Muffin', 30.00),
   (4, 'Biscuit', 40.00);

--Create source table
DECLARE @UpdatedProducts TABLE (
   ProductID INT PRIMARY KEY,
   ProductName VARCHAR(100),
   Rate MONEY
);

INSERT INTO @UpdatedProducts
VALUES   (1, 'Tea', 10.00),
   (2, 'Coffee', 25.00),
   (3, 'Muffin', 35.00),
   (5, 'Pizza', 60.00);
-- DDL and sample data population, end

--SELECT * FROM @Products;
--SELECT * FROM @UpdatedProducts;


--MERGE SQL statement
--Synchronize the target table with
--refreshed data from source table
;MERGE INTO @Products /* WITH (UpdLock, HoldLock) */ AS TARGET  
USING @UpdatedProducts AS SOURCE 
ON (TARGET.ProductID = SOURCE.ProductID) 
-- When rows are matched, update the rows if there is any change
-- but only if something needs to be updated!!!
WHEN MATCHED AND (TARGET.ProductName <> SOURCE.ProductName 
   OR TARGET.Rate <> SOURCE.Rate) THEN 
   UPDATE SET TARGET.ProductName = SOURCE.ProductName, 
         TARGET.Rate = SOURCE.Rate
--When no rows are matched, insert the incoming rows from source
--table to target table
WHEN NOT MATCHED BY TARGET THEN 
   INSERT (ProductID, ProductName, Rate) 
   VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate)
--When there is a row that exists in target table and
--same row does not exist in source table
--then delete this row from target table
WHEN NOT MATCHED BY SOURCE THEN 
   DELETE
--$action specifies a column of type nvarchar(10) 
--in the OUTPUT clause that returns one of three 
--values for each row: 'INSERT', 'UPDATE', or 'DELETE', 
--according to the action that was performed on that row
OUTPUT $action, 
   DELETED.ProductID AS TargetProductID, 
   DELETED.ProductName AS TargetProductName, 
   DELETED.Rate AS TargetRate, 
   INSERTED.ProductID AS SourceProductID, 
   INSERTED.ProductName AS SourceProductName, 
   INSERTED.Rate AS SourceRate;
-- INTO #log (a, dt, what);

/*
We can see the results:
- Coffee rate was updated from 20.00 to 25.00, 
- Muffin rate was updated from 30.00 to 35.00, 
- Biscuit was deleted 
- Pizza was inserted.
*/
SELECT * FROM @Products;