MERGE target Table
USING source Table
On match condition
When matched then
Statement
When isn't matched then
Statement
Where the last statement semicolon can not be omitted, and the source table can be either a table or a subquery statement
When not matched by TARGET
Indicates that the target table does not match, by Target is the default, so we directly use when the matched then
When not matched by SOURCE
Indicates that the source table does not match, that is, the target table exists, the source table does not exist in the case.
Main usage:
Merge cannot update the same row more than once, nor can it update and delete the same row
When the source and target tables do not match:
Insert operation if the data is the source table has no target table
If the data is not in the source table and the target table has it, then update or delete the data operation
When the source and target tables match:
Perform an update operation or delete an operation
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
is 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 inconsistent field
Https://technet.microsoft.com/zh-cn/library/bb510625.aspx This is the MSDN URL.
In the Merge matched operation, only UPDATE or DELETE statements can be allowed to execute.
In the Merge not matched operation, only INSERT statements are allowed.
A matched operation that appears in a Merge statement can only occur once or DELETE statements, otherwise the following error will occur-an action of type ' when matched ' cannot appear more th An once in a ' UPDATE ' clause of a MERGE statement.
The Merge statement must contain a semicolon at the end; End.
SQL Server's Merge function usage