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
