INSERT dbo.MstProduct(ID, Product_ID, Product_Name) VALUES (28, 'MED141', 'Alfimaxin') NULL,Įxecute the following query to add data to the MstProduct table: SET IDENTITY_INSERT dbo.MstProduct ON )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON , 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.įirst create a target table named MstStock and MstProduct on the Product_Master database, located on the TTI412-VM2 server.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.
To perform the above-mentioned task, we will perform the following steps:
Master databases of a software and transactional databases of software are on separate database servers. For example, a pharmaceutical company uses inventory software.
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. Use MERGE when source and target are on a separate server 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. 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. Here records are considered as matching when data within the joined columns are the same. This clause will be used when we want to update or delete the records on the destination table. To modify the data on the target table, MERGE supports following T-SQL clauses.
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įollowing is the format of the MERGE statement: MERGE target t Typically, matching should be done by matching unique identifiers like primary keys. It is like join conditions which are used to match rows. This condition determines how SourceTable matches with destination table. Both tables are compared using the condition, defined in the Merge Statement. It modifies DestinationTable based on the data of SourceTable. The merge statement uses SourceTable and Destinationtable. We do not need to write an individual statement to update changes in the target table. Using MERGE, we can get performance improvement because all three operations (INSERT, UPDATE, and DELETE) are performed in one pass. The following image illustrates how “MERGE” works: Based on how column data matches, changes apply to data of target table. It joins both tables using columns, common in both tables like the primary key. Using it, we can execute INSERT, UPDATE and DELETE on the target tables within a single query block. Using the MERGE statement, we can change data in a target table based on data in a source table.