Yesterday while while working with my current application I got a situation where I have to Insert/Update/Delete records from tables based on some conditions. After searching for the solution in google in came to know MERGE statement of SQL server which does all 3 task INSERT/UPDATE/DELETE at a time based on your condition and i found it very useful so sharing this with you.
In the below example we are updating the data from source table data to target table. For this we have written MERGE statement, that is performing INSERT, UPDATE & DELETE with a single MERGE statement.
MERGE statement performing below operations
- UPDATE : If id matched and name not matched, updating name in target table from source table.
- INSERT : If id not matched in target table inserting row from source table to target table
- DELETE : If id in target table not matched with source table, deleting row from target table
SQL Statement for MERGE:
declare @source table( id int, name varchar(50)) declare @target table( id int, name varchar(50), status varchar(10)) insert into @source values (1, 'abc'), (2,'pqr' ), (3, 'xyz') insert into @target(id, name) values (1, 'abc'), (2,'sdfdf'), (4, 'abc') select * from @target merge @target as a using ( select * from @source ) as b on a.id = b.id when matched and a.name<>b.name then update set a.name = b.name, a.status = 'updated' when not matched by target then insert (id, name, status) values (b.id, b.name, 'inserted') when not matched by source then delete; select * from @target Output: Isn't is good? I like it very useful.