Wednesday, December 26, 2012

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

INSERTINTO
[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

MERGEINTO [Temp] asTARGET

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

SOURCEONTARGET.empId =SOURCE.empId

WHENMATCHEDTHEN

UPDATESET Name=SOURCE.Name, Salary =SOURCE.Salary

WHENNOTMATCHEDTHEN

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

SOURCEONTARGET.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

MERGEINTO [Temp] asTARGET

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

SOURCEONTARGET.empId =SOURCE.empId

WHENMATCHEDTHEN

UPDATESET Name=SOURCE.Name, Salary =SOURCE.Salary

WHENNOTMATCHEDTHEN

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

MERGEINTO [Temp] asTARGET

USING (SELECT empId,Name,Salary from TempTest)AS

SOURCEONTARGET.empId =SOURCE.empId

WHENMATCHEDTHEN

UPDATESET Name=SOURCE.Name, Salary =SOURCE.Salary

WHENNOTMATCHEDTHEN

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

SOURCEONTARGET.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