How to understand the merge statement in T-SQL

Text: 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:

MERGE into Student_target as St--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 are matched, when St.sno=ss.sno, with Ss.sname update st.sname, we see here that update is not indicated later Table, which is updated here is the target table then        update SET St. Sname = ss. Sname      When the not is matched by target-the target table does not exist, and the source table has data, then the insert operation is done, here by Target can be omitted, but it is recommended to add then        insert values< c10/> (ss. Sno,ss. Sname)    when the not matched by   Source-The delete operation is performed when the target table exists and no data exists in the source table, the by sourcethen delete 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) into Student_target as St--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 are matched, when St.sno=ss.sno, with Ss.sname update st.sname, we see here that update is not indicated later Table, which is updated here 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, the insert operation is performed, where by Target can be omitted, but the recommended add then        insert VALUES    (ss. Sno,ss. Sname)    when the not matched by   Source-The delete operation is performed when the target table exists and no data exists in the source table, the by sourcethen delete 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".

Note: The above knot has been solved, you can see the next blog post: How to understand T-SQL in the merge statement (ii)

How to understand the merge statement in T-SQL