What is the MERGE statement?
Using the MERGE statement, we can change data in a target table based on data in a source table. Using it, we can execute INSERT, UPDATE and DELETE on the target tables within a single query block. It joins both tables using columns, common in both tables like the primary key. Based on how column data matches, changes apply to data of target table. The following image illustrates how “MERGE” works:
Using MERGE, we can get performance improvement because all three operations (INSERT, UPDATE, and DELETE) are performed in one pass. We do not need to write an individual statement to update changes in the target table.
The merge statement uses SourceTable and Destinationtable. It modifies DestinationTable based on the data of SourceTable. Both tables are compared using the condition, defined in the Merge Statement. This condition determines how SourceTable matches with destination table. It is like join conditions which are used to match rows.
Typically, matching should be done by matching unique identifiers like primary keys. For example, the Source Table is NewProduct and destination is Productmaster and the primary key is ProductID, then merge condition should be as following:
NewProduct.ProductID=ProductMaster.ProdID
Following is the format of the MERGE statement:
MERGE target t Using source s ON joinCondition WHEN MATCHED THEN updateQuery WHEN NOT MATCHED BY TARGET THEN insertQuery WHEN NOT MATCHED BY SOURCE THEN deleteQuery
To modify the data on the target table, MERGE supports following T-SQL clauses.
- WHEN MATCHED
- WHEN NOT MATCHED [BY TARGET]
- WHEN NOT MATCHED [BY SOURCE]
“WHEN MATCHED” clause
This clause will be used when we want to update or delete the records on the destination table. Here records are considered as matching when data within the joined columns are the same.
“WHEN NOT MATCHED [BY TARGET]” clause
If the record is present in the source table but not in the target table then this clause will be used to insert a new record into the target table.
“WHEN NOT MATCHED [BY SOURCE]” clause
This clause will be used when we want to delete or update a record in a source table which does not match a row in the target table.
Use MERGE when source and target are on a separate server
In this article, I am going to demonstrate how to perform insert, update and delete operation using MERGE, when the source and target tables are in separate servers. For example, a pharmaceutical company uses inventory software. Master databases of a software and transactional databases of software are on separate database servers. The following is a setup:
The company has added an ordered few products. I want to perform a few cleanup processes while updating the stock of products. Following is the list of tasks which must be performed.
- If a product exists in inventory and the same product was ordered, then update then update the stock.
- If a product does not exist in the inventory and add product is ordered than add the product in stock.
- If the product exists in the inventory but it is not ordered moreover the stock of the product is not updated for more than a year than delete the product from inventory.
To perform the above-mentioned task, we will perform the following steps:
- Create a global temporary table named ##Source_Trn_Table. Populate data from “TrnOrder” (Source Table) using the OPENROWSET command and store data in ##Source_Trn_Table.
- Perform INSERT, UPDATE and DELETE operation on the MstStock table (Target Table) using the MERGE keyword, based on the following conditions:
- If the value of the Product_ID column exists in ##Source_Trn_Table and the stock table, then update the current stock in the MstStock table.
- If the value of the Product_ID column exists in ##Source_Trn_Table but does not exist in the MstStock table, then add a product to the MstStock table.
- If the value of the Product_ID column exists in MstStock but does not exist in ##Source_Trn_Table, moreover the column value of last_stock_update_date is greater than a year, then delete product_id from the MstStock table.
Following is the flow chart:
Demonstration
First create a target table named MstStock and MstProduct on the Product_Master database, located on the TTI412-VM2 server. Execute the following query:
USE [Product_Master] GO CREATE TABLE [dbo].[MstProduct]( [ID] [int] IDENTITY(1,1) NOT NULL, [Product_ID] [varchar](15) NULL, [Product_Name] [varchar](500) NOT NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], UNIQUE NONCLUSTERED ( [Product_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[MstStock]( [ID] [int] IDENTITY(1,1) NOT NULL, [Product_ID] [varchar](5) NOT NULL, [Current_Stock] [int] NULL, [Last_Stock_Update_Date] [datetime] NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], UNIQUE NONCLUSTERED ( [Product_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Now add some data to both tables.
Execute the following query to add data to the MstProduct table:
SET IDENTITY_INSERT dbo.MstProduct ON GO INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (28, 'MED141', 'Alfimaxin') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (29, 'MED142', 'Zylasonmuc') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (30, 'MED143', 'Rythmoxabid') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (31, 'MED144', 'Omedrozol') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (32, 'MED145', 'Reducurzol') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (33, 'MED146', 'Losapuritriol') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (34, 'MED147', 'Pipepapren') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (35, 'MED148', 'Miraperahex') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (36, 'MED149', 'Durachloridevant') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (37, 'MED151', 'Renachloridenide') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (38, 'MED152', 'Ecopurimuc') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (39, 'MED153', 'Aerocarpambid') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (40, 'MED154', 'Afsitec') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (41, 'MED155', 'Aprozovant') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (42, 'MED156', 'Levopafen') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (43, 'MED157', 'Medrotraxel') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (44, 'MED158', 'Doxxaliq') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (45, 'MED159', 'Betatasine') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (46, 'MED161', 'Ciclopatex') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (47, 'MED162', 'Acadipiphane') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (48, 'MED163', 'Septomapin') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (49, 'MED164', 'Acioxenal') INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (50, 'MED165', 'Natadrolol') GO SET IDENTITY_INSERT dbo.MstProduct OFF GO
Execute the following query to add data to the MstStock table:
insert into MstStock (Product_ID,Current_Stock,Last_Stock_Update_Date) values ('MED145',15,'2018-10-14'),('MED146',20,'2018-10-13'),('MED147',5,'2018-09-10'),('MED150',5,'2018-08-01'),('MED158',0,'2017-10-14'),('MED159',0,'2017-10-14')
Execute the following “Select” queries to review the output of tables.
Query:
Use Product_Master Go Select * from MstProduct
Output:
Query:
Use Product_Master Go Select * from MstStock
Output:
Secondly, create a source table named TrnOrder on the Inventory_Details database, located on the TTI412-VM1 server. Execute the following query:
USE [Inventory_Details] GO CREATE TABLE [dbo].[TrnOrder]( [ID] [int] IDENTITY(1,1) NOT NULL, [Product_ID] [varchar](15) NOT NULL, [Ordered_Qty] [int] NULL, [Ordered_Date] [datetime] NULL, [Last_Ordered_Date] [datetime] NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], UNIQUE NONCLUSTERED ( [Product_ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO
Execute the following query to add data to the MstStock table:
insert into TrnOrder (Product_ID,Ordered_Qty,Ordered_Date,Last_Ordered_Date)
values
('MED145',10,convert(date,getdate()),'2018-10-14'),('MED146',5,convert(date,getdate()),'2018-10-13'),
('MED147',15,convert(date,getdate()),'2018-09-10'),('MED150',200,convert(date,getdate()),'2018-08-01')
,('MED169',50,convert(date,getdate()),'2018-10-14'),('MED170',100,convert(date,getdate()),'2018-10-14')
Execute the following “Select” query to review the output of the table.
Query:
Use Inventory_Details Go Select * from TrnOrder
Output:
Connect to Remote SQL Server instance to populate data
As I mentioned, we want to update the values in “table which is created on a remote server. We can access data from a remote database server by using the following methods.
- SQL Server Linked server: Linked server is used to execute a command on OLEDB data source which is linked to remote SQL Server instance. Using a linked server, you can also query the different database product like Oracle. OLEDB sources can be configured to access Microsoft Access and Excel as a linked server.
- SQL Server OPENROWSET function: Using the OPENROWSET function, we can execute an Ad-Hoc query on the remote OLEDB data source.
In this article, we will use the OPENROWSET method to access data of the remote table. To query a remote server using the OPENROWSET function, we must enable the Ad hoc Distributed Queries configuration parameter.
“Ad hoc Distributed Queries” is an advanced option, hence firstly we must enable the Show Advanced option configuration parameter. To do that, execute the following command in the Query window of SQL Server management studio.
exec sp_configure 'show advanced options',1 reconfigure with override Go
Once the Show advanced option parameter is enabled, execute the following query to enable Ad hoc Distributed Queries:
sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE WITH OVERRIDE; GO
We cannot use “OPENROWSET” function to perform MERGE operation using data of remote server. To do that first we must import data from remote server and store it in the global temporary table. After that, we can use data lied within the global temporary table to update the target table.
As I mentioned, firstly we must import data from the remote table. To do that, create a temp table and import data using the OPENROWSET function.
The following query will create a global temporary table.
use Product_Master go CREATE TABLE ##Source_Trn_Order ( [ID] [int] IDENTITY(1,1) NOT NULL, [Product_ID] [varchar](15) NOT NULL, [Ordered_Qty] [int] NULL, [Ordered_Date] [datetime] NULL, [Last_Ordered_Date] [datetime] NULL )
Once the temp table is created, let’s load data from a source table which is located on a remote server. To do that, execute the following query:
insert into ##Source_Trn_Order select [Product_ID],[Ordered_Qty],[Ordered_Date],[Last_Ordered_Date] from
OPENROWSET('SQLNCLI', 'Server=TTI609-VM1;Trusted_Connection=yes;',
'SELECT Product_ID, Ordered_Qty, Ordered_Date,Last_Ordered_Date FROM Inventory_Details.dbo.TrnOrder') AS a;
Step 1: If a product exists in MstStock (Target Table) and TrnOrder (Source Table) then update the current quantity in MstStock
To do that, use the WHEN MATCHED clause. The clause joins Source and Target tables on the common columns of both tables. The Product_ID column is common between MstStock and ##Source_Trn_Table, hence use it to join both tables.
Execute the following code:
MERGE MstStock target_Stock USING ##Source_Trn_Order Source_Order ON target_Stock.Product_Id = Source_Order.Product_Id WHEN MATCHED THEN UPDATE SET target_Stock.Current_Stock = Source_Order.Ordered_Qty + target_Stock.Current_Stock, Last_Stock_Update_Date=getdate();
Value of Current_Stock column of 4 products should be updated. Execute the following query to verify the output:
select b.Product_ID,b.Product_Name,a.Current_Stock,a.Last_Stock_Update_Date from MstStock a inner join MstProduct b on a.Product_ID=b.Product_ID and a.Current_Stock>0
Following is the output:
Step 2: If a Product does not exist in MstStock (Target Table), then add it in MstStock (Target Table)
Pharmacy store had ordered a few products. Those products were added in MstProduct table but not added to the MstStock table. To add those products in the MstStock table, I will use the WHEN NOT MATCHED [TARGET] clause. The clause joins source and target tables using common columns. If matching rows are not found in the target table than it inserts rows from the source table.
To add products to MstStock using the MERGE table, execute the following code:
MERGE mststock target_Stock
using ##source_trn_order Source_Order
ON target_Stock.product_id = source_order.product_id
WHEN matched THEN
UPDATE SET target_Stock.current_stock = Source_Order.ordered_qty
+ target_Stock.current_stock,
last_stock_update_date = Getdate()
WHEN NOT matched BY target THEN
INSERT (product_id,
current_stock,
last_stock_update_date)
VALUES (Source_Order.product_id,
Source_Order.ordered_qty,
Getdate());
Two products IDs, MED169 and MED170, should be added. Execute the following query to review output:
select b.Product_ID,b.Product_Name,a.Current_Stock,a.Last_Stock_Update_Date from MstStock a inner join MstProduct b on a.Product_ID=b.Product_ID and a.Current_Stock>0
Following is output:
Step 3: Delete Item from MstStock (Target Table), if current stock in MstStock (Target Table) is zero and product is not in ##Source_Trn_Order (source table)
In inventory, there are few products which need to be deleted because those were not ordered since a year. Hence, we must delete them from the MstStock table and the MstProducts table. To delete those products from the MstStock table, we can use WHEN NOT MATCHED [SOURCE].
The WHEN NOT MATCHED [SOURCE] clause joins source and target tables using common columns. If matching rows are not found in the source table than it deletes rows from the target table.
To remove products from the MstStock table, execute the following code:
MERGE mststock target_Stock
using ##source_trn_order Source_Order
ON target_Stock.product_id = source_order.product_id
WHEN matched THEN
UPDATE SET target_Stock.current_stock = Source_Order.ordered_qty
+ target_Stock.current_stock,
last_stock_update_date = Getdate()
WHEN NOT matched BY target THEN
INSERT (product_id,
current_stock,
last_stock_update_date)
VALUES (Source_Order.product_id,
Source_Order.ordered_qty,
Getdate())
WHEN NOT matched BY SOURCE THEN
DELETE;
Two products IDs, MED158 and MED159 should be added. Execute the following query to review output:
select b.Product_ID,b.Product_Name,a.Current_Stock,a.Last_Stock_Update_Date from MstStock a inner join MstProduct b on a.Product_ID=b.Product_ID and a.Current_Stock>0
Following is output:
Summary
In this article I have covered as follows:
- What is MERGE keyword and how it works?
- Different clauses used in MERGE to update the source and target table.
- How to modify data using MERGE keyword when databases are on different servers.
Useful tools:
dbForge Data Compare for SQL Server – powerful SQL comparison tool capable of working with big data.
