MERGE command is used to perform INSERT, UPDATE OR DELETE operations on a target table based on the results from the source table. On finding match, we can then specify certain actions on it.
If a match is found, update operations are performed; else insertions are done.
It is important for you to specify at least one of the matched sources, however you can provide at max, three matched sources.
If more than one matched source is provided, it can be specified in any order.
NOTE- A variable cannot be updated more than once in a matched clause.
When inserting, updating or deleting a data it has to be taken into reference, that during all these operations all the constraints remain valid, including any cascading referential integrity constraint. This means that you will be able to perform the DML operations only if you don’t violate any constraint.
The MERGE statement requires a semicolon (;) as a terminator.
ADVANTAGE OF MERGE –
The biggest advantage with MERGE is that you can perform INSERT, UPDATE and DELETE operations on one go of the database table. That is, when parsing table once you can perform all the three operations. Unlike earlier, where to perform these operations individually, the table had to be parsed n number of times.
SYNTAX –
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,…n ] ) ]
;
EXAMPLE –
CREATE TABLE A(
COLUMN1 INT,
COLUMN2 VARCHAR(20)
);
INSERT INTO A VALUES
(1,’AVANTIKA’),
(2,’ANAMIKA’),
(3,’ANTRA’),
(4,’AVNI’)
CREATE TABLE B(
COLUMN1 INT,
COLUMN2 VARCHAR(10)
)
INSERT INTO B VALUES
(1,’AVINASH’),
(3,’ANIMESH’),
(5,’ANISH’),
(7,’ANTRA’)
OUTPUT –
SELECT * FROM A
COLUMN1 COLUMN2
1 AVANTIKA
2 ANAMIKA
3 ANTRA
4 AVNI
SELECT * FROM B
COLUMN1 COLUMN2
1 AVINASH
3 ANIMESH
5 ANISH
7 ANTRA
MERGE A AS [TARGET]
USING B AS [SOURCE]
ON TARGET.COLUMN1=SOURCE.COLUMN1
WHEN MATCHED
THEN
UPDATE
SET COLUMN1=[SOURCE].COLUMN1,
COLUMN2=[SOURCE].COLUMN2
WHEN NOT MATCHED BY TARGET
THEN
INSERT
VALUES(
SOURCE.COLUMN1,
SOURCE.COLUMN2
);
SELECT * FROM A;
OUTPUT
1 AVINASH
2 ANAMIKA
3 ANIMESH
4 AVNI
5 ANISH
7 ANTRA
Happy Learning 🙂
Tanubhrt.This.Side 
Leave a comment