Merge statement and insert data into another table

Question

Merge statement and insert data into another table

Sudip Bhatt 2,281

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

Viorel 126.9K Reputation points

2020-11-22T19:48:52.037+00:00

Maybe you can use triggers for some tasks.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2020-11-22T19:59:03.06+00:00

Maybe. But triggers are good for things like "if someone updates the location name, I also want X to happen". But it is not good for "when I update locationName in this specific process only, I also what X to happen". My interpretation is that Mr. Bhatt is rather into the latter, but I could be wrong.

Answer accepted by question author

1 additional answer

Your answer

Viorel 126.9K Reputation points

2020-11-22T19:48:52.037+00:00

Maybe you can use triggers for some tasks.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2020-11-22T19:59:03.06+00:00

Maybe. But triggers are good for things like "if someone updates the location name, I also want X to happen". But it is not good for "when I update locationName in this specific process only, I also what X to happen". My interpretation is that Mr. Bhatt is rather into the latter, but I could be wrong.

Answer 1

Erland Sommarskog 133.9K MVP Volunteer Moderator

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)
   ;

0 comments

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

EchoLiu-MSFT 14,626 Reputation points

2020-11-23T02:40:19.643+00:00

If this does not solve your problem, could you share with us your specific problem and its examples?

Echo

Share via

Merge statement and insert data into another table

1 additional answer

Your answer