Why we use MERGE statement in SQL?
In a typical data warehousing application, regularly amid the ETL cycle you have to perform INSERT, UPDATE and DELETE operations on a TARGET table by coordinating the records from the SOURCE table. For instance, an items measurement table has data about the items; you have to match up this table with the most recent data about the items from the source table. You would need to compose separate INSERT, UPDATE and DELETE proclamations to revive the objective table with an overhauled item list or do lookups. In spite of the fact that it is by all accounts straight forward at first look, yet it gets to be unwieldy when you have do it all the time or on different tables, even the execution debases fundamentally with this methodology. In this tip we will stroll through how to utilize the MERGE explanation and do this in one pass.
In SQL 2008 you can perform insert, update, or delete operations in a single statement using the MERGE statement . The MERGE statement allows you to join a data source table with a target table or view, and then perform multiple actions against the target based on the results of that join. For example, you can use the MERGE statement to perform the given operations below.
Operation can perform using MERGE
- According condition insert,update,delete row in target table.If the row exists in the target table, update one or more columns; otherwise, insert the data into a new row.
- Synchronize two tables.Insert, update, or delete rows in a target table based on differences with the source table .
Clauses(keyword) using in MERGE Query
- MERGE - The MERGE clause(keyword) specifies the table or view that is the target of the insert, update, or delete operations.
- USING - The USING clause(keyword) specifies the data source table being joined with the target table.
- ON - The ON clause(keyword) specifies the join conditions that determine where the target table and source table match.
- WHEN - The WHEN clause(keyword) (WHEN MATCHED, WHEN NOT MATCHED BY TARGET, and WHEN NOT MATCHED BY SOURCE) specify the actions to take based on the results of the ON clause(keyword) and any additional search criteria specified in the WHEN clauses.
- OUTPUT - The OUTPUT clause(keyword) returns a row for each row in the target table that is inserted, updated, or deleted.
Syntax for MERGE Query:-
- MERGE [AS TARGET]
- USING [AS SOURCE]
- ON
- [WHEN MATCHED
- THEN ]
- [WHEN NOT MATCHED [BY TARGET]
- THEN ]
- [WHEN NOT MATCHED BY SOURCE
- THEN ];
Simple Example with Query
Step 1 Create two table source and target table and insert data.
- --Create a target table
- CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY )
- GO
- --Insert records into target table
- INSERT INTO Products VALUES (1, 'Tea', 10.00), (2, 'Coffee', 20.00), (3, 'Muffin', 30.00), (4, 'Biscuit', 40.00)
- GO
- --Create source table
- CREATE TABLE UpdatedProducts ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100), Rate MONEY )
- GO
- --Insert records into source table
- INSERT INTO UpdatedProducts VALUES (1, 'Tea', 10.00), (2, 'Coffee', 25.00), (3, 'Muffin', 35.00), (5, 'Pizza', 60.00)
- GO
- SELECT * FROM Products
- SELECT * FROM UpdatedProducts
- GO



Bhavesh JadavPosted Jan 28, 2019, 5:01 AM
Nice blog sir, Can you please create a post on fast from Merge statement and single insert, update or delete a record.
Bikesh SrivastavaPosted Nov 11, 2016, 1:54 PM
Yes sure wait for next article .
Manav PandyaPosted Nov 11, 2016, 1:45 AM
Nice share sir and post some concept about SSRS
SubashPosted Sep 14, 2016, 12:25 AM
Nice
Bikesh SrivastavaPosted Jul 21, 2016, 7:01 AM
Its helpful to understand