Merge statement and insert data into another table

Question

I have very basic question about Merge statement. see the sample code.

MERGE Locations T
USING Locations_stage S ON T.LocationID=S.LocationID
WHEN MATCHED THEN
UPDATE SET LocationName=S.LocationName;

When Match then updating Location target table but if i want to insert / update data into another few tables when match then can i do it ?

i am talking about this

    MERGE Locations T
    USING Locations_stage S ON T.LocationID=S.LocationID
    WHEN MATCHED THEN
    UPDATE SET LocationName=S.LocationName;
    Insert Into AnotherTable1(col1,col2) values ('val1','val2')
    UPDATE AnotherTable2 Set col3='val3' where <condition>

When match then target table will be updated and i want to insert and update data into another table. is it possible ?

please guide me. thanks

Answer 1

No, you can't. An INSERT, UPDATE, DELETE and MERGE statement can only operate on a single table.

I'm not sure exactly what you want to do, but you can use the OUTPUT clause to save data affected by the statement into a table and work from there. For instance:

    MERGE Locations T
     USING Locations_stage S ON T.LocationID=S.LocationID
     WHEN MATCHED THEN
     UPDATE SET LocationName=S.LocationName
    OUTPUT inserted.LocationName, S.LocationId INTO #mytemp(LocationName, LocationId)
   ;

Answer 2

Hi @Sudip Bhatt ,

The merge statement can only insert, update, and delete a single target table.
Please refer to the following statement to insert or update another table according to the conditions:

--Insert data into the third table  
Insert Into AnotherTable1(col1,col2)   
select t.col1,t.col2 from Locations t  
join Locations_stage s   
ON t.LocationID=s.LocationID  
  
--Update the third table based on the two tables  
UPDATE AnotherTable2   
Set col3='val3'   
from Locations T  
join Locations_stage S ON T.LocationID=S.LocationID  
where <condition>  

Regards
Echo

---

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 November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table