Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Insert/update/delete records in a single statement

, 23 Feb 2012
Rate this:
Please Sign up or sign in to vote.
How we can write a single statement to perform insert, update and delete operation
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. Smile | :)
 
Regards,
Praveen

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Praveen Meghwal
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 23 Feb 2012
Article Copyright 2012 by Praveen Meghwal
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid