Insert/update/delete records in a single statement

In SQL Server, sometimes we require to perform all DML operations (Insert, Update, and Delete) in a single statement. How to achieve it in SQL Server? The answer is using MERGE Statement.

Following is the SQL statement using which you can achieve this:

DECLARE @Student TABLE (StudentId INT, StudentName VARCHAR(100))
INSERT INTO @Student VALUES(1,'Student 1')
INSERT INTO @Student VALUES(2,'Student 2')
INSERT INTO @Student VALUES(3,'Student 3')
INSERT INTO @Student VALUES(5,'Student 5')

SELECT * FROM @Student

DECLARE @Student2 TABLE (StudentId INT, StudentName VARCHAR(100))
INSERT INTO @Student2 VALUES(1,'Student 1 From 2')
INSERT INTO @Student2 VALUES(2,'Student 2 From 2')
INSERT INTO @Student2 VALUES(3,'Student 3 From 2')
INSERT INTO @Student2 VALUES(4,'Student 4 From 2')
INSERT INTO @Student2 VALUES(5,'Student 5 From 2')

SELECT * FROM @Student2

MERGE @Student AS S1
USING (SELECT StudentId, StudentName FROM @Student2) AS S2 ON S1.StudentId = S2.StudentId
WHEN MATCHED AND S1.StudentName = 'Student 5' THEN DELETE
WHEN MATCHED THEN UPDATE SET S1.StudentName = S2.StudentName
WHEN NOT MATCHED THEN INSERT (StudentId, StudentName) VALUES (S2.StudentId, S2.StudentName);

SELECT * FROM @Student

Here I have created 2 table variables with the same schema and inserted some records in both the tables.

I have written a MERGE statement to update or insert records in table @Student with the records present in table @Student2, and delete the record with StudentName = 'Student 5'.

You might be wondering why I have kept the record which needs to be deleted in both the tables. The answer to this is Delete operation is not allowed in NOT MATCHED condition, i.e., if you want to perform delete operation, record has to be present in both the tables.

Hope this tip will help many of you. :)

Regards,
Praveen