T SQL 合并示例需要帮助理解 [英] T SQL merge example needed to help comprehension

问题描述

以下内容:

MERGE dbo.commissions_history AS target
USING (SELECT @amount, @requestID) AS source (amount, request)
ON (target.request = source.request)
WHEN MATCHED THEN
    UPDATE SET amount = source.amount
WHEN NOT MATCHED THEN
    INSERT (request, amount)
    VALUES (source.request, source.amount);

来自 https://stackoverflow.com/a/2967983/857994 是一种非常漂亮的插入方式/更新(并删除一些添加的工作).我发现即使在谷歌搜索之后也很难理解.

from https://stackoverflow.com/a/2967983/857994 is a pretty nifty way to do insert/update (and delete with some added work). I'm finding it hard to follow though even after some googling.

有人可以吗:

• 用简单的术语解释一下 - 在这种情况下，MSDN 文档毁了我的大脑.
• 告诉我如何修改它，以便用户可以输入数量和值请求而不是从另一个数据库位置选择它们?

基本上，我想使用它从 C# 应用程序插入/更新，其中包含从我获得的 XML 文件中获取的信息.因此，我需要了解如何通过这种机制手动制定查询以将解析的数据导入数据库.

Basically, I'd like to use this to insert/update from a C# app with information taken from XML files I'm getting. So, I need to understand how I can formulate a query manually to get my parsed data into the database with this mechanism.

推荐答案

如果您不熟悉 join 语句 那么这就是您需要开始的地方.了解联接的工作原理是其余部分的关键.一旦您熟悉了连接，那么理解合并是最容易的，将其视为一个完整的连接，其中包含有关如何处理匹配或不匹配的行的说明.

If you aren't familiar with join statements then that is where you need to start. Understanding how joins work is key to the rest. Once you're familiar with joins then understanding the merge is easiest by thinking of it as a full join with instructions on what to do for rows that do or do not match.

所以，使用提供的代码示例让我们看一下commissions_history 表

So, using the code sample provided lets look at the table commissions_history

|  Amount  |   Request  |   <other fields> |
--------------------------------------------
|  12.00   |   1234     |   <other data>   |
|  14.00   |   1235     |   <other data>   |
|  15.00   |   1236     |   <other data>   |

合并语句在称为目标"的表和返回称为源"的表(或逻辑上与 CTE 等表非常相似的结果集)的表达式之间创建完整连接.

The merge statement creates a full join between a table, called the "target" and an expression that returns a table (or a result set that is logically very similar to a table like a CTE) called the "source".

在给出的示例中，它使用变量作为源，我们假设这些变量已由用户设置或作为参数传递.

In the example given it is using variables as the source which we'll assume have been set by the user or passed as a parameter.

DECLARE @Amount Decimal = 18.00;
DECLARE @Request Int = 1234;

MERGE dbo.commissions_history AS target       
USING (SELECT @amount, @requestID) AS source (amount, request)       
ON (target.request = source.request)   

将其视为连接时创建以下结果集.

Creates the following result set when thought of as a join.

|  Amount  |   Request  |   <other fields> | Source.Amount | Source.Request  |
------------------------------------------------------------------------------
|  12.00   |   1234     |   <other data>   |   18.00       |     1234        |
|  14.00   |   1235     |   <other data>   |   null        |     null        |
|  15.00   |   1236     |   <other data>   |   null        |     null        |

在找到匹配项的情况下，使用给出的关于如何处理目标的说明.

Using the instructions given on what to do to the target on the condition that a match was found.

WHEN MATCHED THEN        
UPDATE SET amount = source.amount    

生成的目标表现在看起来像这样.请求 1234 的行更新为 18.

The resulting target table now looks like this. The row with request 1234 is updated to be 18.

|  Amount  |   Request  |   <other fields> |
--------------------------------------------
|  18.00   |   1234     |   <other data>   |
|  14.00   |   1235     |   <other data>   |
|  15.00   |   1236     |   <other data>   |

自从找到匹配项后，没有其他事情发生.但是可以说，来自源的值是这样的.

Since a match WAS found nothing else happens. But lets say that the values from the source were like this.

DECLARE @Amount Decimal = 18.00;
DECLARE @Request Int = 1239;

结果连接如下所示:

|  Amount  |   Request  |   <other fields> | Source.Amount | Source.Request  |
------------------------------------------------------------------------------
|  12.00   |   1234     |   <other data>   |   null        |     null        |
|  14.00   |   1235     |   <other data>   |   null        |     null        |
|  15.00   |   1236     |   <other data>   |   null        |     null        |
|  null    |   null     |   null           |   18.00       |     1239        |

由于在目标中没有找到匹配的行，该语句执行另一个子句.

Since a matching row was not found in the target the statement executes the other clause.

WHEN NOT MATCHED THEN                                 
INSERT (request, amount)                                 
VALUES (source.request, source.amount);  

导致目标表现在看起来像这样:

Resulting in a target table that now looks like this:

|  Amount  |   Request  |   <other fields> |
--------------------------------------------
|  12.00   |   1234     |   <other data>   |
|  14.00   |   1235     |   <other data>   |
|  15.00   |   1236     |   <other data>   |
|  18.00   |   1239     |   <other data>   |

合并语句真正的潜力在于当源和目标都是大表时.因为它可以用一个简单的语句对每一行进行大量的更新和/或插入.

The merge statements true potential is when the source and target are both large tables. As it can do a large amount of updates and/or inserts for each row with a single simple statement.

最后一点.重要的是要记住 not matching 默认为完整子句 not matching by target，但是您可以在适当的位置指定 not matching by source属于或附加于默认条款.合并语句支持两种类型的不匹配(源中的记录不在目标中，或者目标中的记录不在源中，如 on 子句所定义).您可以在 MSDN 上找到完整的文档、限制和完整的语法.

A final note. It's important to keep in mind that not matched defaults to the full clause not matched by target, however you can specify not matched by source in place of, or in addition to, the default clause. The merge statement supports both types of mismatch (records in source not in target, or records in target not in source as defined by the on clause). You can find full documentation, restrictions, and complete syntax on MSDN.

这篇关于T SQL 合并示例需要帮助理解的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！