In earlier versions of SQL Server in order to perform INSERT, UPDATE or DELETE we have to have separate T-SQL statements. With the introduction of SQL Server 2008 we have MERGE statement which helps in simplifying this. It allows us to perform DML operations based on certain JOIN conditions in single statement.
The Merge statement basically has two tables namely source table and destination table. We have to perform a join between them using a certain condition. If the condition matches certain action is performed in the target table. If the condition doesnt matches some other action is performed and so on.
Let us consider the following example to demonstrate the usage of MERGE statement:
The following is the syntax for MERGE statement,
MERGE [ TOP ( expression ) [ PERCENT ] ] [ INTO ] target_table [ WITH ( ) ] [ [ AS ] table_alias] USING ON [ WHEN MATCHED [ AND ] THEN ] [ WHEN NOT MATCHED [ BY TARGET ] [ AND ] THEN ] [ WHEN NOT MATCHED BY SOURCE [ AND ] THEN ] [ ] [ OPTION ( [ ,...n ] ) ];
I am creating the following 2 tables namely Vendor and VendorAddress and populating data into them.
CREATE TABLE Vendor ( VendorId INT PRIMARY KEY, VendorName VARCHAR(50), Accountnumber INT ) GO INSERT INTO Vendor VALUES (1, 'ABC Corp', 58401), (2, 'XYZ Corp', 69875), (3, 'GS Corp', 79865), (4, 'MNC Corp', 89475), (5, 'FBA Corp', 98898), (6, 'MPL Corp', 56789) GO CREATE TABLE VendorAddress ( VendorId INT, Address VARCHAR(50) ) GO INSERT INTO VendorAddress VALUES (1, 'Texas Dallas'), (2, 'Tamil Nadu Chennai'), (3, 'Seattle'), (4, 'Texas Austin'), (5, 'New York'), (7, 'Jacksonville') GO
The output of the below command will display the Vendors and their addresses available.
SELECT * FROM Vendor SELECT * FROM VendorAddress
The following MERGE statement will join the target table VendorAddress with Vendor table which is the source on the condition VendorId in both the tables. When the condition MATCHES, Address field in Vendor table is UPDATED. The condition NOT MATCHED THEN means the condition does not provide any records in the target table, the record is present in the source but not in target. In that condition an INSERT is done in the target table. The condition NOT MATCHED BY SOURCE means the condition does not provide any records in the source table, the record is present in the target but not in source. In that condition a DELETE against the target table is done which will delete those extra records present in the target table.
To find what records are updated, inserted or deleted while running a MERGE statement we use OUTPUT clause.
MERGE INTO VendorAddress AS VA USING ( SELECT VendorId, VendorName FROM Vendor ) AS V (VendorId, VendorName) ON VA.VendorId=V.VendorId WHEN MATCHED THEN UPDATE SET VA.Address= 'USA Seattle' WHEN NOT MATCHED THEN INSERT (VendorId, Address) VALUES(6, 'India Chennai') WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, Inserted.VendorId, Inserted.Address, Deleted.VendorId, Deleted.Address;
As we can see there are 5 updates done since there are 5 records from 1 to 5 (VendorID) which are updated. Similarly there will be a single insert statement for VendorId 6 since the record is present only in source and not in target. A record will be deleted for VendorId 7 since its present only in the target and not in source table.
Thus the MERGE statement simplifies the amount of work needed to implement an update and insert logic and minimizes the amount of code that needs to be written.
Leave a Reply