Part 69 Merge in SQL Server

Text version of the video

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

Slides

All SQL Server Text Articles

All SQL Server Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

What is the use of MERGE statement in SQL Server
Merge statement introduced in SQL Server 2008 allows us to perform Inserts, Updates and Deletes in one statement. This means we no longer have to use multiple statements for performing Insert, Update and Delete. With merge statement we require 2 tables
1. Source Table - Contains the changes that needs to be applied to the target table
2. Target Table - The table that require changes (Inserts, Updates and Deletes)

The merge statement joins the target table to the source table by using a common column in both the tables. Based on how the rows match up as a result of the join, we can then perform insert, update, and delete on the target table.

Merge statement syntax
MERGE [TARGET] AS T
USING [SOURCE] AS S
ON [JOIN_CONDITIONS]
WHEN MATCHED THEN
[UPDATE STATEMENT]
WHEN NOT MATCHED BY TARGET THEN
[INSERT STATEMENT]
WHEN NOT MATCHED BY SOURCE THEN
[DELETE STATEMENT]

Example 1 : In the example below, INSERT, UPDATE and DELETE are all performed in one statement
1. When matching rows are found, StudentTarget table is UPDATED (i.e WHEN MATCHED)
2. When the rows are present in StudentSource table but not in StudentTarget table those rows are INSERTED into StudentTarget table (i.e WHEN NOT MATCHED BY TARGET)
3. When the rows are present in StudentTarget table but not in StudentSource table those rows are DELETED from StudentTarget table (i.e WHEN NOT MATCHED BY SOURCE)

Create table StudentSource
(
ID int primary key,
Name nvarchar(20)
)
GO

Insert into StudentSource values (1, 'Mike')
Insert into StudentSource values (2, 'Sara')
GO

Create table StudentTarget
(
ID int primary key,
Name nvarchar(20)
)
GO

Insert into StudentTarget values (1, 'Mike M')
Insert into StudentTarget values (3, 'John')
GO

MERGE INTO StudentTarget AS T
USING StudentSource AS S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE SET T.NAME = S.NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, NAME) VALUES(S.ID, S.NAME)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

Please Note : Merge statement should end with a semicolon, otherwise you would get an error stating - A MERGE statement must be terminated by a semi-colon (;)

In real time we mostly perform INSERTS and UPDATES. The rows that are present in target table but not in source table are usually not deleted from the target table.

Example 2 : In the example below, only INSERT and UPDATE is performed. We are not deleting the rows that are present in the target table but not in the source table.

Truncate table StudentSource
Truncate table StudentTarget
GO

Insert into StudentSource values (1, 'Mike')
Insert into StudentSource values (2, 'Sara')
GO

Insert into StudentTarget values (1, 'Mike M')
Insert into StudentTarget values (3, 'John')
GO

MERGE INTO StudentTarget AS T
USING StudentSource AS S
ON T.ID = S.ID
WHEN MATCHED THEN
UPDATE SET T.NAME = S.NAME
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, NAME) VALUES(S.ID, S.NAME);

[theraizadatalks]

I'll always be grateful to you for creating this SQL playlist free for people like us. Thanks Venkat

[prashantakarte]

Hi Venkat,
I went through your complete series of SQL tutorial & I found it very much useful in my day to day work. I couldn't resist myself on commenting about your videos. They are really nice. Thanks for your efforts. I'm looking forward to see videos on QTP test automation tool.
May god bless you!..
Thanks

[denvernaidoo]

I found what I was looking for in the first 2 minutes of your video. Thank you for explaining so clearly.

[QuickZ_]

Once again you succeed to explain something where everyone else fails. Thank you!

[alturker]

I love you Venkat, I grew up with your videos when I was Jr. You are my hero!

[tedbrick]

Great great thanks for the great course, I spent dozens of hours learning not only SQL Server technologies but also technical English listening your course. The way of teaching is beautiful and amazing. Thanks for your efforts, thanks from Poland.

[yogev-drushimil]

and again you did it, you took something I thought was complicated and manage to simplify it. genius ! Thank you.

[reddyabhik]

Excellent explanation with example on SQL Merge statement.

[kanishmishra]

Hello Venkat Sir,
I m fan of your. Your way of explanation is awesome. Ty for your tutorials.

[weiwei]

Thanks a lot, Venkat. This series of videos are really informative and most importantly they are free. Thank you for your efforts making these videos, Thanks again, God bless you

[MaxQuagliotto]

Very concise and helpful! All SQL Server example documentation should be this concise.

[vivekgondaliya]

Immensely helpful tutorial. Thanks for the upload. Keep up the good work. Thanks.

[btulkas]

Venkat,
Thank you so much for this video, you answered a specific question I had about "when not matched by source"

[ShreyasKumar]

Hi venkat thanks for your tutorials. you made me to learn SQL with ease. Thank a lot :) cheers

[krzysztofs]

I love the style of your teaching. Excellent. Bravo !

[tigoogoo]

these are the best tutorials on youtube. thank you!

[guotinghust]

Thank you so much. The series of videos really help a lot! 

[qfxkfge]

Thank you! If I saw your tutorials before, I wouldn't have so many troubles with SQL Server now. Gotta fix it just now! Thank you for your work! Subscription is from me.

[dbin]

another clear and concise explanation. Thank you.

[omkarsakhalkar]

Hi Venkant,

Your videos are very helpfull. Can you please upload the videos on SSIS, SSRS and SSAS. Thank you Very Much.