Hey guys welcome back to the SQL tutorial series. In this session we will
work with the merge statement. So the merge statement helps us to perform
insert update and delete in just one statement. This means we no longer
require multiple statements to perform insert update and delete. And for the
merge statement we required two tables; a source table and a target table. So the
so table basically contains all the changes which are to be applied to the
target table and the target table is that table upon which we'll be
implementing all the changes. Now the merge statement basically joins the
target table to the source table using a common column in both the tables and
based on how the rules match up you can perform insert delete and update
operations on the target table. Now let's have a look at the syntax of the merge
statement so we'll start with the merge keyword and then give the name of the
target table and assign it an alias using the ask keyword then we'll type
using and give the name of the source table and also assign it an alias using
the ask keyword again. After that we'll type on and then set the join condition
then we'll type when matched so if the row is match up based on the join
condition then we'll go ahead and update the target table after that we check
when not match by target so this basically means that if there are some
rows which are present in the source table but not in the target table then
we'll go ahead and insert those rows from the source table into the target
table and finally we check when not matched by source. So here we are
checking if there are some rows which are present in the target table but not
in the source table. Then we'll go ahead and delete those rows from the target
table and for the practical purpose we'll be using these two tables. So this
is our source table with the name employee source and this is a target
table with the name employee target so using the merge statement we'll be
updating the a salary and e age columns in the
table wherever there's a match also insert the records with employee ID 7
and employee ID 8 into the target table from the source table and delete the
records with employee ID 4 and employee ID 5 from the target table because
they're not present in the source table. So let's go to SQL server and work
with the merge statement right so we have our two tables right in front of us this
is the employee source table and this is the employ target table and we also have
the merge syntax to apply on these two tables. So we'll type merge and then give
the name of the target table employee target we'll assign it an alias D
similarly we'll give the employee source table and then assign it an alias S
after that we'll type on and then set the join condition so join condition is
t dot e ID is equal to s dot e ID so we basically set the join condition on the
employee ID columns of the source table and the target table. Then we have when
matched so there is a match between the rows of these two tables then we'll go
ahead and update the target table so here in the update statement we have T
dot e salary equals s dot e salary and T dot e age is equal to s dot e age. So if the
records match then I'll be setting the values of the salary column of the
target table to be equal to the values of the salary column of the source table
similarly I'll also set the values of the age column of the target table to
be equal to the values of the age column of the source table and then we
have when not matched by target. So here we are checking if there are some rows
which are present in the source table but not in the target table and if that
is the case we'll go ahead and insert all of those into these columns of the
target table and the values would be coming from the source table. So this
employee ID column from the target table would be getting the values from the
employee ID column of the source table similarly this employee name column of
target table would be getting its values from the employee name column of this
source table and finally we'll check when not matched by source. So if there
are some rows which are present in the target table but not present in the
source table then we'll just delete all of those rows. I'll select all of this
and I'll click on execute so you see that 8 rows have been affected. Now
let me type the select statement and have a glance at the modified employee
target table, select start from employee target I'll select this I'll click on
execute right so this is a modified employee target table. So we also have
our original employee target table over here so let me compare these two tables
so we see that initially Sam's salary was $95,000 and his age was 45 and
after applying the merge statement his salary change to 93 thousand dollars and
his age change from 45 to 40 similarly Ann's initial salary was one
hundred and twenty five thousand dollars and after applying the merge statement
her salary changed to hundred and thirty thousand dollars and then with
the merge statement we have also inserted the records with employee ID 7
and employee ID 8 and deleted these two records from the
employee target table. So this is how we can work with the merge statement.
Thanks for attending the session. Let's meet in the next class.