Usage of the merge function in SQLServer, sqlservermerge

Usage of the merge function in SQLServer, sqlservermerge

The Merge keyword is a magic DML keyword. It is introduced in SQL Server 2008. It can combine Insert, Update, and Delete into a simple sentence. MSDN's explanation of Merge is very short and concise: "the target table is inserted, updated, or deleted based on the result of the connection to the source table.

MERGE target table

USING source table

ON Matching condition

WHEN MATCHED THEN

Statement

WHEN NOT MATCHED THEN

Statement;

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

WHEN NOT MATCHED BY TARGET

It indicates that the TARGET table does NOT match. by target is the default value. Therefore, we use the WHEN NOT MATCHED THEN

WHEN NOT MATCHED BY SOURCE

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

Main usage:

Merge cannot update or delete the same row multiple times.

If the source table does not match the target table: if the data is from the source table and the target table does not, insert the data. If the data is from the source table but the target table does not, update or delete data when the source table and target table match: when the update operation or delete operation is performed, the when matched clause can have two. when there are two, the first clause must be when matched and condition, and the two matched clauses only execute one, and the two clauses must be an update and a delete operation. when not matched by source is similar to the above

Merge icr_codemap_bak as
Using icr_codemap as B
On a. COLNAME = B. COLNAME and a. ctcode = B. ctcode
When matched and B. pbcode <> a. pbcode
Then update set a. pbcode = B. pbcode
When not matched
Then insert values (B. colname, B. ctcode, B. pbcode, B. note)
;

You can compare different fields for updates.

Https://technet.microsoft.com/zh-cn/library/bb510625.aspx. This is the site of MSDN.

In the Merge Matched operation, only UPDATE or DELETE statements can be executed.
In the Merge Not Matched operation, only INSERT statements can be executed.
A Matched operation in a Merge statement can only contain one UPDATE or DELETE statement, otherwise, the following error will occur-An action of type 'when matched' cannot appear more than once in a 'update' clause of a MERGE statement.
The Merge statement must end with a semicolon.

Ps: usage of Merge-using in 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 -- if it is not matched, it cannot be update (if no matching is successful, it cannot be updated) then insert (UserName, Sex) values ('zhangzhao', 'B ');

After execution

The combination of Merge and using is used for statistics and analysis of BI data. For example, if the child table does not have data, you must delete the corresponding data in the parent table. the method is to run a job and then use the cursor, Table value function, temporary table, and so on to obtain data cyclically and update the parent table. This is a waste of efficiency, and Merge will be used in this case.