Merge Statement one of the way to synch destination from source table TIP# 58

 

Problem:-  Suppose you have a source table and one destination table and you want to synch destination table from source table. means if record is already exists then you need to update and the record with latest value and insert the record if record is not exists.

Solution:-  Although there are various ways exist but here we are using a new feature came in SQL Server 2008 which is MEGE statement.

Suppose we have a  tblStudentSource table and one tblStudentDestination table

both have equal columns as shown in below figure.

both have studentId, Firstname, LastName & course column

 

if you see above figure you will find that destination table there are 2 rows while in tblstudentSource table we have 3 rows.

Now if you see in tblStudentDestination LastName of StudentID  2 is spelled wrong.

So what we have to do here. We have to Insert a new row from tblStudentSource and update existing row.

To achieve this we will use merge statement as shown in figure

Now if you see above snap  we write basic merged statement which check condition of tblSource and destination table and if condition is matched then we wrote update statement if not matched then we wrote insert statement.

MERGE tblStudentDestination AS Destination
USING tblStudentSource  AS Source
ON Source.StudentId = Destination.StudentId
WHEN MATCHED THEN
UPDATE
SET Destination.FirstName = Source.FirstName,
    Destination.LastName = Source.LastName,
    Destination.Course = Source.Course
WHEN NOT MATCHED THEN
INSERT (FirstName,LastName,Course)
VALUES (Source.FirstName,source.LastName,Source.Course);
    

Now we run above statement we got the desire result and our source and destination table is in synched.

I hope this post may help and give you a general Idea of merge statement.

Thanks!!!

RJ!!!