SQL Server's Merge function usage

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.