The MERGE statement is a powerful feature in SQL Server that allows you to perform multiple data modification operations in a single statement. In previous versions of SQL Server, you had to write separate statements for INSERT, UPDATE, and DELETE operations based on certain conditions. However, with the MERGE statement, you can include the logic for these data modifications in one statement.

One of the most significant advantages of the MERGE statement is that it reads and processes the data only once. In previous versions, you had to write three different statements to process three different activities (INSERT, UPDATE, or DELETE). With the MERGE statement, all update activities can be done in one pass of the database table, resulting in improved query performance.

The syntax of the MERGE statement is as follows:

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])]

Let’s consider an example to understand how the MERGE statement works. We have two tables, “StudentDetails” and “StudentTotalMarks”. We want to delete records with marks greater than 250, update the marks by adding 25 to each record, and insert records that do not exist.

-- Create StudentDetails table
CREATE TABLE StudentDetails
(
StudentID INTEGER PRIMARY KEY,
StudentName VARCHAR(15)
)

-- Insert records into StudentDetails table
INSERT INTO StudentDetails
VALUES(1, 'SMITH')
INSERT INTO StudentDetails
VALUES(2, 'ALLEN')
INSERT INTO StudentDetails
VALUES(3, 'JONES')
INSERT INTO StudentDetails
VALUES(4, 'MARTIN')
INSERT INTO StudentDetails
VALUES(5, 'JAMES')

-- Create StudentTotalMarks table
CREATE TABLE StudentTotalMarks
(
StudentID INTEGER REFERENCES StudentDetails,
StudentMarks INTEGER
)

-- Insert records into StudentTotalMarks table
INSERT INTO StudentTotalMarks
VALUES(1, 230)
INSERT INTO StudentTotalMarks
VALUES(2, 255)
INSERT INTO StudentTotalMarks
VALUES(3, 200)

-- Merge process for the two tables
MERGE StudentTotalMarks AS stm
USING (SELECT StudentID, StudentName FROM StudentDetails) AS sd
ON stm.StudentID = sd.StudentID
WHEN MATCHED AND stm.StudentMarks > 250 THEN DELETE
WHEN MATCHED THEN UPDATE SET stm.StudentMarks = stm.StudentMarks + 25
WHEN NOT MATCHED THEN
INSERT(StudentID, StudentMarks)
VALUES(sd.StudentID, 25)

After executing the MERGE statement, we can compare the previous result set and the new result set to verify if our three conditions were carried out. In this example, five rows were updated. StudentID 2 was deleted as it had marks greater than 250. 25 marks were added to all existing records (StudentID 1 and 3), and the records that did not exist (StudentID 4 and 5) were inserted into the StudentTotalMarks table.

The MERGE statement is a handy improvement for T-SQL developers who need to update database tables with complex logic. It not only simplifies the code by combining multiple operations into one statement but also improves the performance of the database by processing the data only once.