Shulou(Shulou.com)05/31 Report--

This article is to share with you about how to use the merge function in SQLServer, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

The Merge keyword is a magic DML keyword. It was introduced in SQL Server 2008, and it can simply combine Insert,Update,Delete into one sentence. MSDN's explanation of Merge is very short: "insert, update, or delete the target table based on the result of the join with the source table."

MERGE goal table

USING source table

ON matching condition

WHEN MATCHED THEN

Statement

WHEN NOT MATCHED THEN

Statement

The semicolon of the last statement cannot be omitted, and the source table can be either a table or a subquery statement.

WHEN NOT MATCHED BY TARGET

Indicates that the target tables do not match, and BY TARGET is the default, so we use WHEN NOT MATCHED THEN directly above

WHEN NOT MATCHED BY SOURCE

Indicates that the source table does not match, that is, it exists in the target table and does not exist in the source table.

Main usage:

Merge cannot update the same row multiple times, nor can it update and delete the same row

When the source table and the target table do not match: if the data is that the source table has a destination table or not, insert operation is performed. If the data is not available in the source table but not in the destination table, update or delete data operations are performed. When the source table and the destination table match, there can be two when matched clauses. When there are two, the first clause must be when matched and condition and only one of the two matched clauses will be executed. And the two clauses must be a update and a delete operation when not matched by source similar to the above

Merge icr_codemap_bak as ausing icr_codemap as bon a.COLNAME = b.COLNAME and a.ctcode = b.ctcodewhen matched and b.pbcode a.pbcodethen update set a.pbcode = b.pbcodewhen not matchedthen insert values (b.colnamediary b.ctcodediary b.pbcodebookb.note)

Can be updated compared to inconsistent fields

Https://technet.microsoft.com/zh-cn/library/bb510625.aspx, this is the website of MSDN.

In Merge Matched operations, only UPDATE or DELETE statements are allowed. In Merge Not Matched operations, only INSERT statements are allowed. For a Matched operation that occurs in a Merge statement, there can only be one UPDATE or DELETE statement, otherwise the following error will occur-the An action of type 'WHEN MATCHED' cannot appear more than once in a' UPDATE' clause of a MERGE statement.Merge statement must end with a semicolon.

The usage of Merge-using in ps:SQL Server

Before execution:

Merge into UserInfo uusing chartinfo c on u.UserId=c.UserIdwhen matched and u.UserName=c.UserName then update set u.lastUpdate=c.LastUpdatewhen not matched-cannot be update when not matched (of course you can't update without a match) then insert (UserName,Sex) values ('ZhangZhao','b')

After execution

The combination of Merge and using is used for data statistics and analysis on BI, such as requiring data that does not exist in the child table, then delete the corresponding data in the parent table to ensure that the data corresponding to the child table and the parent table correspond to the usual practice is to run a job and then update the parent table through the cursor / table-valued function / temporary table cycle. This is a waste of efficiency when Merge comes in handy.

The above is how to use the merge function in SQLServer. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.