Click here to Skip to main content
12,760,618 members (31,885 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


5 bookmarked
Posted 22 Feb 2012

Insert/update/delete records in a single statement

, 23 Feb 2012 CPOL
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. :)



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


About the Author

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

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170217.1 | Last Updated 23 Feb 2012
Article Copyright 2012 by Praveen Meghwal
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid