Part 69 Merge in SQL Server
Text version of the video
csharp-video-tutorials.blogspo...
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 KZread channel. Hope you can help.
/ @aarvikitchen5572
Slides
csharp-video-tutorials.blogspo...
All SQL Server Text Articles
csharp-video-tutorials.blogspo...
All SQL Server Slides
csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
kzread.info...
All Dot Net and SQL Server Tutorials in Arabic
/ kudvenkatarabic
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);
Пікірлер: 81
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
Once again you succeed to explain something where everyone else fails. Thank you!
I found what I was looking for in the first 2 minutes of your video. Thank you for explaining so clearly.
I'll always be grateful to you for creating this SQL playlist free for people like us. Thanks Venkat
I love you Venkat, I grew up with your videos when I was Jr. You are my hero!
@Csharp-video-tutorialsBlogspot
8 жыл бұрын
+al turker Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. Dot Net & SQL Server training videos for web developers kzread.infoplaylists?view=1&sort=dd You can order DVDs for offline viewing using the link below www.pragimtech.com/Order.aspx Code Samples & Slides are on my blog csharp-video-tutorials.blogspot.com Tips to effectively use our channel kzread.info/dash/bejne/q2tskq-wmLydYNY.html To receive email alerts, when new videos are uploaded, please subscribe to our channel kzread.info Please click that THUMBS UP button below the video, if you like the videos Thanks a million for sharing these resources with your friends Best Venkat
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.
and again you did it, you took something I thought was complicated and manage to simplify it. genius ! Thank you.
Hi venkat thanks for your tutorials. you made me to learn SQL with ease. Thank a lot :) cheers
Hello Venkat Sir, I m fan of your. Your way of explanation is awesome. Ty for your tutorials.
Immensely helpful tutorial. Thanks for the upload. Keep up the good work. Thanks.
I love the style of your teaching. Excellent. Bravo !
Very concise and helpful! All SQL Server example documentation should be this concise.
Thank you, its getting better and better.
Venkat, Thank you so much for this video, you answered a specific question I had about "when not matched by source"
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.
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
Excellent explanation with example on SQL Merge statement.
these are the best tutorials on youtube. thank you!
Thank you so much. The series of videos really help a lot!
another clear and concise explanation. Thank you.
God bless you sir for the work you are doing for us without thinking of any gain
Thank you venkat, it really helped me to understand the usage of merge query
This was great, and if you did not bring this subject "Merge" I would not know it in spite Of I was doing SQL for quiet sometime, So Thank you again and again.
You are better than many textbooks
Amazing tutorial series...Thanks a ton....Cheers!!!!!!
Perfect and helpful as usual.
Fantastic..god job!
another great tutorial Venkat. Excellent work as always
Amazing tutorials.. Thanks a lot ...!!!
Wow This is is a good functionality After OLTP work using this in nightly batch may be good. I enjoyed the video and recommend to others. One thing I observed that in this video that you mentioned the applicable version. My suggestion every time a video is made on what software version it is based is very important to audiences since lot of versions are jumping very fast. Thanks for educating the community and appreciate your volunteer-ship. Thanks a lot
dude, you are amazing! I am subscribing now
Simply Glorious Explanation.
Hi Venkat sir ,Thanks for your videos
Amazing teacher! Thanks so much
God bless you :D
I like the way you explain
How simply explained. Thankyou for this tutorial
Revisiting. Thanks for educating the community Thanks a lot
@kishorkshirsagar2643
5 жыл бұрын
Very clean and clear explanation thank you
Well done!
Hi Venkant, Your videos are very helpfull. Can you please upload the videos on SSIS, SSRS and SSAS. Thank you Very Much.
@jtopu
9 жыл бұрын
yes i agree with you ,would you pls make videos on SSIS, SSRS and SSAS.Thanks
Excellent!
super condensed, super simple, just super :) thx
great !, I don't have words to say
Thank you very much. Very clear.
Very clear and concise explanation
Good explanation
Thanks for the tutorial 🤗
Thank you!
tremendously doing nice effort.. could please sir upload Crystal Report videos...thank you
Clearly explained
finally i got the point, thanks!
@akshayshetye8718
7 жыл бұрын
yeah!! me too..
well explained
5 years later, thank you very much.
at 1:10 is all I need, thank you!
hi vanket! i just want to say you from my heart "LOVE YOU". you are my hero !!!!!!!!!!!!!!!!!!!!!!
I got the below err // when i merge condition is having sometime 1 or 5more records it that the cause ?? The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
can uu upload the videos how to handle the large number of servers at a time its means about server handling
Nice
Hi venkat, Can you please explain, why merge statement ends with semi-colon?
Could you please suggest how to add new seed data/lookup data into two tables which has foreign key relationship. These tables already has some data. I want to add new lookup data to existing data. Please suggest. How to use Merge . Any other ways to setup lookup data
So, if I want to insert multiple rows into the our existing "target" table, we have to create another table? It seems like a waste of space, just to create an additional table to insert a few rows.
@jonkc3635
6 жыл бұрын
oh, you can do this: MERGE INTO [Language] AS Target USING (VALUES (1, 'en', 'English'), (2, 'fr', 'French'), (3, 'zh', 'Chinese') ) AS .................
Hello Can we compare particular one column for merge ?
I want to move tat deleted record(John) into a separate table and then delete it here . Is it possible?
Precisely in this situation it's like Dropping Target and renaming Source to Target Name
Hello sir, please teach about how to merge two databases in sql server
What if we have 50 columns in source and target then how do we update all the columns?
Anyone after 2020?
but is it thread safe, that is the real question.
Hi Sir, how can we use this merge to update more than one column ? Please help
@ashishchinna9201
Жыл бұрын
If your source is s, target is t and has columns (id, productname, price), then you can do the following Update Set t.prodcutname=s.productname, t.price=s.price This will update multiple columns in the target table with the corresponding source table values.
3:52 By default value
learned Merge at tutorial nr 69 coincidence ? i think not
I am not liking the video