Generally we write separate statements to INSERT, UPDATE or DELETE data based on certain conditions. But now in SQL Server 2008 there is a new feature present called MERGE statement using which we can INSERT, UPDATE or DELETE record in one statement.
MERGE is a new feature which Microsoft has introduced with SQL Server 2008 that provides an efficient way to perform multiple DML operations.
In previous version of SQL server, we had to write separate statements to INSERT, UPDATE or DELETE data based on certain conditions, but now using MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.
One of the most imortant advantage of MERGE statement is all the data is read and processed only once.The MERGE statement internally works as an individual insert, update and delete statement within a single Merge statement.You need to specify the SOURCE and the TARGET table or query which should be joined together.
MERGE WITH INSERT AND UPDATE
CREATE PROCEDURE MERGE_INSERT_UPDATE
SET NOCOUNT ON;
MERGE USERS AS target
USING (SELECT @USERNAME, @ROOM) AS source (USERNAME, ROOM)
ON (target.USERNAME = source.USERNAME)
WHEN MATCHED THEN
UPDATE SET ROOM = source.ROOM
WHEN NOT MATCHED THEN
INSERT (USERNAME, ROOM)
VALUES (source.USERNAME, source.ROOM)
OUTPUT deleted.*, $action, inserted.* ;
EXEC MERGE_INSERT_UPDATE @USERNAME = 'Charlie bB', @ROOM = 'New ROOM';
MERGE WITH DELETE AND UPDATE
CREATE PROCEDURE MERGE_WITH_UPDATE_DELETE
MERGE Rooms AS target
USING (SELECT Users.ID,Users.RENT FROM Users
ON Users.ID = Rooms.ID
) AS source (ID,RENT)
ON (target.ID = source.ID)
WHEN MATCHED AND target.RENT <= 450
THEN UPDATE SET target.RENT = 5000
OUTPUT $action, Inserted.ID, Inserted.RENT, Deleted.ID,
EXEC MERGE_WITH_UPDATE_DELETE ;