MERGE (Transact-SQL)

MERGE (Transact-SQL 2008)

MERGE is new feature , it Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Examples

CREATE TABLE TABLE1
(
Monthid INTEGER,
Month_Name VARCHAR(15)
)
GO
INSERT INTO TABLE1
VALUES(1,'JAN')
INSERT INTO TABLE1
VALUES(2,'FEB')
INSERT INTO TABLE1
VALUES(3,'MAR')
INSERT INTO TABLE1
VALUES(4,'APR')
INSERT INTO TABLE1
VALUES(5,'MAY')
GO

CREATE TABLE TABLE2
(
Monthid INTEGER,
Month_Name VARCHAR(15)
)
GO
INSERT INTO TABLE2
VALUES(1,'JAN')
INSERT INTO TABLE2
VALUES(6,'JUNE')
INSERT INTO TABLE2
VALUES(7,'JULY')

select * from TABLE2
select * from TABLE1

MERGE TABLE1 AS t1
USING (SELECT Monthid,Month_Name FROM TABLE2) AS t2
ON t1.Monthid = t2.Monthid
WHEN MATCHED AND t1.Monthid > 8 THEN DELETE
WHEN MATCHED THEN UPDATE SET t1.Monthid = t2.Monthid
WHEN NOT MATCHED THEN
INSERT(Monthid,Month_Name)
VALUES(Monthid,Month_Name);
GO

MSDN:
Using MERGE to perform INSERT and UPDATE operations on a table in a single statement

A common scenario is updating one or more columns in a table if a matching row exists, or inserting the data as a new row if a matching row does not exist. This is usually done by passing parameters to a stored procedure that contains the appropriate UPDATE and INSERT statements. With the MERGE statement, you can perform both tasks in a single statement. The following example shows a stored procedure that contains both an INSERT statement and an UPDATE statement. The procedure is then modified to perform the equivalent operations by using a single MERGE statement.