Merge Statement in SQL | How to Merge Statements in SQL | Intellipaat


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.

1 thought on “Merge Statement in SQL | How to Merge Statements in SQL | Intellipaat

  1. Got a question on how to merge statement in SQL server? Do write it in the comment and you will get a response immediately by our expert. For MS SQL Server training & certification, call us at US: 1800-216-8930 (Toll Free) or India: +917022374614. You can also write us at [email protected]

Leave a Reply

Your email address will not be published. Required fields are marked *