How to use MERGE, WHEN MATCH and WHEN NOT MATCH statement in SQL Server 2008

How to use MERGE, WHEN MATCH and WHEN NOT MATCH statement in SQL Server 2008

Normally we have common requirement for inserting data into the database table, we need to take care of duplicate data should not get inserted, for this we used to first check in the database table whether that data is present or not, If not then insert otherwise update.

Current Approcah

Declare       @Id         Int = 10,       @Name       Varchar(20) = 'Kesharwani',       @Salary     Int = '30000' -- Update the row if it exists.        UPDATE [Temp]       SET Name = @Name, Salary = @Salary       WHERE empId = @empId -- Insert the row if the UPDATE statement failed.           IF (@@ROWCOUNT = 0 )       BEGIN           INSERT INTO [Temp] (Id, Name,Salary)           VALUES (@empId, @Name,@Salary)       END

New way to do the same operation

Let say you have one database table as below

Select * from Temp

Query -1

Declare       @Id         Int = 10,       @Name       Varchar(20) = 'Kesharwani',       @Salary     Int = 30000            MERGE INTO [Temp] as TARGET       USING (SELECT @Id AS empId,@Name AS Name,@Salary AS Salary) AS SOURCE ON TARGET.empId = SOURCE.empId       WHEN MATCHED THEN             UPDATE SET Name=SOURCE.Name, Salary =SOURCE.Salary       WHEN NOT MATCHED THEN             INSERT (empId,Name,Salary) VALUES (SOURCE.empId,SOURCE.Name,SOURCE.Salary);

Note: If you have multiple PK columns then in the USING statement you need to include all PK columns as same as below

USING (SELECT @Id,@Name,@Salary ) AS

SOURCE ON TARGET.empId = SOURCE.empId
      AND TARGET.empId1 = SOURCE.empId1
      AND TARGET.empId2 = SOURCE.empId2    

Result from Query-1

Employee Id = 10 data was present that’s why that row is UPDATED

Select * from Temp

Query -2

Declare       @Id         Int = 30,       @Name       Varchar(20) = 'Master',       @Salary     Int = 40000               MERGE INTO [Temp] as TARGET       USING (SELECT @Id AS empId,@Name AS Name,@Salary AS Salary) AS SOURCE ON TARGET.empId = SOURCE.empId       WHEN MATCHED THEN             UPDATE SET Name=SOURCE.Name, Salary =SOURCE.Salary       WHEN NOT MATCHED THEN             INSERT (empId,Name,Salary) VALUES (SOURCE.empId,SOURCE.Name,SOURCE.Salary);

Result from Query-2

Employee Id= 30 was NOT present that’s why new data got INSERTED

Select * from Temp

Multiple values as an input

Let’s say you are passing table type as input parameter because you want to pass multiple data together, assume you are passing TempTest table (having same structure as Temp table) as an input .

Select * from TempTest

Query -3

MERGE INTO [Temp] as TARGET       USING (SELECT empId,Name,Salary from TempTest) AS SOURCE ON TARGET.empId = SOURCE.empId       WHEN MATCHED THEN             UPDATE SET Name=SOURCE.Name, Salary =SOURCE.Salary       WHEN NOT MATCHED THEN             INSERT (empId,Name,Salary) VALUES (SOURCE.empId,SOURCE.Name,SOURCE.Salary);

Note: If you have multiple PK columns then in the USING statement you need to include all PK columns as same as below

USING (SELECT @Id,@Name,@Salary ) AS

SOURCE ON TARGET.empId = SOURCE.empId
      AND TARGET.empId1 = SOURCE.empId1
      AND TARGET.empId2 = SOURCE.empId2

Result from Query-3

From TempTest table employee Id =10 was present that’s why this data got UPDATED and employee Id =30 was NOT present then this data got INSERTED into the Temp table

Select * from Temp

You can perform all kinds of operation (INSERT, UPDATE, DELETE) using these statement , for more example see the link here http://technet.microsoft.com/en-us/library/bb510625.aspx