Part 69 Merge in SQL Server — Transcript

Full transcript

1. 0:00this is part 69 of sequel server
2. 0:01tutorial in this video we will discus
3. 0:04merge statement in sequel server what is
4. 0:06the use of merge statement merge
5. 0:08statement introduced in sequel server
6. 0:092008 allows us to perform inserts
7. 0:12updates and deletes in one statement
8. 0:14this means we no longer have to use
9. 0:16multiple statements for performing
10. 0:18insert update and delete with merge
11. 0:21statement we require two tables a source
12. 0:23table and a target table source table is
13. 0:26the table that contains the changes that
14. 0:28needs to be applied to the target table
15. 0:30target table is the table that require
16. 0:33changes it is the table upon which we
17. 0:35will be performing inserts updates and
18. 0:37delat merge statement joins the target
19. 0:40table to the source table by using a
20. 0:42common colum in both the tes based on
21. 0:45how the rows mat up we can then perform
22. 0:47an insert update or delete on the target
23. 0:50table let's look at an example now here
24. 0:53we have got student source and student
25. 0:55target tes student target table is the
26. 0:57table on which we will be performing in
27. 1:00update and delete all in one statement
28. 1:02we will be using the id colum in both
29. 1:05the tes to join them now when the rows
30. 1:08mat between the tabl we want to perform
31. 1:11an update on the target table look at
32. 1:13the data that we have at the moment in
33. 1:15both the tes we have a rec with id equ 1
34. 1:19so those rs mat and we want to do an
35. 1:21update meaning we want to upd m m to m
36. 1:26so when r mat we want to perform update
37. 1:30row do not mat that is when the rows are
38. 1:32present in the source table but not in
39. 1:35the target table we want to insert all
40. 1:37such rows into the target table at the
41. 1:40moment s record with id equ 2 is present
42. 1:43only in the source table it not present
43. 1:46in the target table so we want to insert
44. 1:49that record into the target table and
45. 1:51finally when the rows are present only
46. 1:54in the target table but not in the
47. 1:56source table we want to delete all su
48. 1:59rows from the tg t at the with the tg t
49. 2:04we have rec with id not in sce table so
50. 2:08we want to delete it from the target
51. 2:11table so after we execute the merge
52. 2:13statement this is how we want the output
53. 2:15to no m record is updated to m m updated
54. 2:21to m s rec which is present only in the
55. 2:24source table but not in the target table
56. 2:26now inserted into the target table joh
57. 2:29rec which present only in the target
58. 2:31table but not in the source table is
59. 2:33deleted from the target table and here
60. 2:36is the syntax to do it so merge we
61. 2:39specify the target table so in our case
62. 2:41the target table is student target as
63. 2:44and we give it an al t and then using
64. 2:47the source table so in our case the
65. 2:50source table is student source and we
66. 2:52give it an alas s on we specify the join
67. 2:56con so how are we joining these two tbl
68. 2:59using the id colum in both the table so
69. 3:02we are here we are using the ales t id =
70. 3:06sid when matched so when the r mat based
71. 3:10on that con then we want to perform an
72. 3:12update on the target table so we are
73. 3:15saying update set t name so name colum
74. 3:19with the target table equals name colum
75. 3:22from the s t so when the r mat perform
76. 3:26an update when not matched by target
77. 3:29this means when the rows are present in
78. 3:32the source table but not in the target
79. 3:35table then we want to do an iner so h we
80. 3:39have iner statement iner we specify the
81. 3:42colum name id and name and vales for
82. 3:45those are going to come from source
83. 3:48table s id and s name now by target is
84. 3:53the defa so you may or may not spey that
85. 3:57h when not matched by source this means
86. 4:01when the rows are present in the target
87. 4:03table but not in the source table then
88. 4:07we want to perform a delete so pretty
89. 4:10straight forward syntax there let's look
90. 4:12at this in action let's flip to sequel
91. 4:14server management studio so i have
92. 4:16already created these two tbl here is
93. 4:19the sequel script to create them i have
94. 4:21the script on my blog in case you need
95. 4:23it at the moment if you look at the data
96. 4:26this is the target table and this is the
97. 4:29source table after we execute this merge
98. 4:32statement we want the data in the target
99. 4:35table to be the same as that of the
100. 4:37source table so let go execute this
101. 4:39merge
102. 4:41statement and let select the dat back
103. 4:43look at that know the dark table is
104. 4:46updated so m rec is updated s rec is
105. 4:49inserted and john rec which was already
106. 4:52present in the target table is deleted
107. 4:55so iner update and delete all in a sle
108. 5:01merge statement and one important thing
109. 5:03to remember is that merge statement
110. 5:05should end with a sem colon if not you
111. 5:08will get an error so when we execute
112. 5:10that look at that a merge statement must
113. 5:12be terminated by a semicon so make sure
114. 5:15you have one right in real
115. 5:19time we mostly perform inserts and
116. 5:22updates we don't delete data that is
117. 5:25present only in the target table but not
118. 5:26in the source table usually the source
119. 5:29table provides us with new recs that are
120. 5:31added and any recs that need to be
121. 5:34updated okay so you know we only perform
122. 5:38inss and updates and if that's
123. 5:40requirement you can omit this last
124. 5:42condition so it's the same quy as before
125. 5:47except that we are deleting that last
126. 5:50con so in this case what going to do
127. 5:52going to perform an update for the
128. 5:54matching r so m m will be updated to m s
129. 5:57record which is present only in sce
130. 5:59table but not in the destination table
131. 6:01will be inserted into the target table
132. 6:04but john record which is present only in
133. 6:06the target table but not in the source
134. 6:07table will be left you know untouched so
135. 6:11it will not be deleted so here we are
136. 6:13only doing an update and insert so let's
137. 6:17look at that in action quickly so after
138. 6:20we execute this merge statement you know
139. 6:23m should be updated to m s record should
140. 6:25be inserted and john record should be as
141. 6:28it is so let flip toqu management stud
142. 6:31now let actually tr the data that is
143. 6:34already there with the source t and
144. 6:36destination table let's execute these
145. 6:39inert statement so we bring it back to
146. 6:42how the data is before so that's our
147. 6:45source and that's our destination and
148. 6:48let's delete this last condition let's
149. 6:52and this merge statement with a
150. 6:54semicolon and let's execute
151. 6:57this now let's select dat back no that m
152. 7:01m upd m s rec ined and joh record is not
153. 7:09delat for
154. 7:12day