SQL Server in the merge function usage detailed _mssql

The merge keyword is a magical DML keyword. It was introduced in SQL Server 2008, and it can be insert,update,delete simple and a sentence. MSDN's explanation for the merge is very concise: "Inserts, updates, or deletes are performed on the target table based on the results of joins to the source table."

MERGE target Table

USING source Table

On matching Criteria

When matched THEN

Statement

When not matched THEN

Statement

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

When isn't matched by TARGET

Indicates that the target table does not match and by Target is the default, so we use the when not matched THEN directly above

When isn't matched by SOURCE

Indicates that the source table does not match, that is, a situation in the target table that does not exist in the source table.

Main usage:

The merge cannot update the same row multiple times, and cannot update and delete the same row

When the source table and the target table do not match: If the data is a source table there is no target table, the insert operation is performed if the data is the source table and the target table is there, update or delete the data action when the source and target tables match: Update or delete when matched this clause can have two, when there are two, The first clause must be when matched and condition and two matched clauses will only execute one, and two clauses must be an update and a delete operation when not matched by source and similar above

Merge Icr_codemap_bak as a
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)
;

can be updated compared to field inconsistencies

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

In the Merge matched operation, only the UPDATE or DELETE statement can be allowed to execute.
In the Merge not matched operation, only INSERT statements are allowed to execute.
The matched operation that occurs in a Merge statement can only occur once for an UPDATE or DELETE statement, or the following error occurs-an action of type ' when matched ' cannot appear more th An once in a ' UPDATE ' clause of a MERGE statement.
The Merge statement must finally contain a semicolon to; End.

Usage of merge-using in Ps:sql server

Prior to execution:

Merge into UserInfo u
using Chartinfo C in U.userid=c.userid when
matched and U.username=c.username
 then Te set U.lastupdate=c.lastupdate
when is not matched-cannot be matched for update (no match succeeded of course not update)
 then insert ( Username,sex) VALUES (' Zhangzhao ', ' B ');

After execution

The merge and using collocation are used for data statistics and analysis on the BI, for example, to require data that is not in the child table, then the parent table should delete the corresponding data to ensure that the child table and the parent table data corresponding if the normal practice is to run a job then through the cursor/table value function/ Temporary tables, and so on loop fetching data and then updating the parent table This is a waste of efficiency. Then the merge comes in handy.