How to understand the merge statement in T-SQL

Written in front of the words: Before the merge statement, feel no use, can be used in other ways to replace, recently looked at the merge statement, it is very useful, can write a lot less code, it looks very compact, of course, there are other advantages.

= = = Text starts =====

SQL Server 2008 introduces the merge keyword, in which you can perform an INSERT, UPDATE, delete operation on a single statement to manipulate the target object data with the data of one source object . Note that the source object and target object are in black, and the source and target objects can actually be not only table tables, but also temporary tables, views, table variables, CTE, and target objects can be SELECT statements . Say so much actually want to express the merge statement can be very flexible to use, but we understand that the "source object" and "target object" can be imagined as a table, after all, temporary tables, views, table variables, CTE can also be imagined as table.

(Note: If the target object is a view, then the action on the target object, such as update, is actually working on the table that generated the view.)

Well, after reading the above text, you may have not looked down, the reason is written above, is to appear rigorous some, followed by examples to explain it, this example does not involve business logic, can focus on understanding the use of the merge, as to the actual when used, can only self-awareness, good, began to cite examples.

For example, there is a student_target table, as follows Table I. , another Student_source table, as follows Table Two :

Execute the following SQL statement:

--Here is the target table, which will be the source table merge USING Student_source as SS   --Here is the source table on St. Sno = ss. Sno --here is the match condition,    when matched-when and then is the matching, when St.sno=ss.sno, with Ss.sname update st.sname, we see this The updated table is not indicated after update, the target table is updated and then update        SET St. Sname = ss. Sname      when the not matched by target-the target table does not exist, and the data exists in the source table, then the insert operation is done, here by Target can be omitted, but the proposed plus        then INSERT VALUES    (ss. Sno,ss. Sname)    when the not is matched by source   --If the target table exists and no data exists in the source table, then the delete operation is performed, where the by source and thendelete is used;    

Above the meaning of the SQL statement can look at the following comments, here to do a brief explanation: for table one, sno=1 row and table two sno=1 match, so the row in table one is updated, table one sno=2,3 in table two does not exist, so delete, table two sno=4 but does not exist in table one, So insert, the final result is as follows:

We see the above results and table two content is the same, in fact, you analyze the above SQL statement, the logic is to put the contents of table two into table one, table one and table two inconsistent data deletion, it seems that our above merge statement appears to be redundant. Here are a few more things to explain:

(1) When we matched, when the not matched by target, when the not matched by source are written, in fact, is optional, we can only use the parts according to their own needs.

(2) in front of the merge into Student_target as St, you can actually add top (n) to operate on a specific number of rows. Execute the following sql:

Merge TOP (2)--here is the target table, which is going to be the source table mergeUSING Student_source   as SS--here is the source table on St. Sno = ss. Sno --here is the match condition,    when matched-when and then is the matching, when St.sno=ss.sno, with Ss.sname update st.sname, we See here the update does not indicate the updated table, here update is the target table  then update        SET St. Sname = ss. Sname      when the not matched by target-the target table does not exist, and the data exists in the source table, then the insert operation is done, here by Target can be omitted, but the proposed plus        then INSERT VALUES    (ss. Sno,ss. Sname)    when the not is matched by source   --If the target table exists and no data exists in the source table, then the delete operation is performed, where the by source and thendelete is used;    

The final results are as follows:

So, add top (2), then the target table can be manipulated only 2 lines, the above update operation one, insert operation one, to reach 2, so the subsequent delete does not affect. Therefore, the top (n) should be able to understand correctly.

(3) Front when matched can actually work with other conditions, such as when matched can be modified to when matched and ss.sno=1 or when matched and st.sno=1; for when not MATC Hed, can only use the source column , that is, increase and ss.sno=1 can, but increase and st.sno=1 will be an error.

(4) Merge must end with a semicolon, indicating that the merge sentence is complete.

The previous operation we see the default is the operation of the target table, and sometimes we want to operate on the target table, we can also operate on the source table for specific conditions, it is possible to work with the OUTPUT clause to complete the operation we want. Here the output sentence is not only for the merge statement, for INSERT, UPDATE, delete and other operations can also be used, so the specific can go to study the output clause alone.

At this point, this article should also be over, how to use the merge statement should also be no problem. But there is still a knot in the heart, as the previous article wrote: How to understand the group by and aggregation functions in the group by and the aggregation function of the same, although it is not a problem, but always hope to find a fanciful idea, can be a different perspective to understand. For example why is basically when matched behind with Update,when not matched by target behind with Insert,when not matched by source behind with delete? Why can't I follow delete after not matched by Target? Why can't I follow insert after not matched by source? Of course, there may be other questions, the current can be combined with the join should be able to explain clearly, but now still can not use very good text logic to express clearly, back to think well how to write again, please forgive me and "cranky".

How to understand the merge statement in T-SQL