需要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 是执行插入/更新(并通过一些添加的工作来删除).即使经过一番谷歌搜索，我仍然很难遵循.
有人可以请
- 简单地解释一下-在这种情况下，MSDN文档毁了我的大脑.
- 告诉我如何修改它，以便用户输入金额&的值.请求，而不是从另一个数据库位置选择它们?
基本上，我想使用它从C#应用程序插入/更新，并从我获取的XML文件中获取信息.因此，我需要了解如何使用这种机制手动制定查询条件，以将解析后的数据输入数据库.
如果您不熟悉
在给定的示例中，它使用变量作为源，我们假设该变量已由用户设置或作为参数传递. 在被认为是联接时创建以下结果集. 使用有关在找到匹配项的情况下对目标采取的操作的说明. 现在生成的目标表如下所示.请求1234的行更新为18. 由于一场比赛，WAS发现没有其他事情发生.但是可以说，源中的值就是这样. 生成的联接如下所示: 由于在目标中找不到匹配的行，因此该语句将执行other子句. 现在生成的目标表如下所示: merge语句的真正潜力是当源和目标都是大表时.因为它可以用一个简单的语句为每一行进行大量更新和/或插入. 最后的笔记.重要的是要记住， The following: 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. Can someone please: 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. 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. So, using the code sample provided lets look at the table commissions_history 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. Creates the following result set when thought of as a join. Using the instructions given on what to do to the target on the condition that a match was found. The resulting target table now looks like this. The row with request 1234 is updated to be 18. Since a match WAS found nothing else happens. But lets say that the values from the source were like this. The resulting join would look like this: Since a matching row was not found in the target the statement executes the other clause. Resulting in a target table that now looks like this: 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. A final note. It's important to keep in mind that 这篇关于需要T SQL合并示例以帮助理解的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！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)
| 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 |
WHEN MATCHED THEN
UPDATE SET amount = source.amount
| Amount | Request | <other fields> |
--------------------------------------------
| 18.00 | 1234 | <other data> |
| 14.00 | 1235 | <other data> |
| 15.00 | 1236 | <other data> |
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 |
WHEN NOT MATCHED THEN
INSERT (request, amount)
VALUES (source.request, source.amount);
| Amount | Request | <other fields> |
--------------------------------------------
| 12.00 | 1234 | <other data> |
| 14.00 | 1235 | <other data> |
| 15.00 | 1236 | <other data> |
| 18.00 | 1239 | <other data> |
not matched默认为完整子句not matched by target，但是您可以指定not matched by source来代替默认子句或在默认子句之外进行指定. merge语句支持两种类型的不匹配(由on子句定义，源中的记录不在目标中，或者源中的记录不在目标中). 您可以在MSDN上找到完整的文档，限制和完整的语法. 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);
| Amount | Request | <other fields> |
--------------------------------------------
| 12.00 | 1234 | <other data> |
| 14.00 | 1235 | <other data> |
| 15.00 | 1236 | <other data> |
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)
| 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 |
WHEN MATCHED THEN
UPDATE SET amount = source.amount
| Amount | Request | <other fields> |
--------------------------------------------
| 18.00 | 1234 | <other data> |
| 14.00 | 1235 | <other data> |
| 15.00 | 1236 | <other data> |
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 |
WHEN NOT MATCHED THEN
INSERT (request, amount)
VALUES (source.request, source.amount);
| Amount | Request | <other fields> |
--------------------------------------------
| 12.00 | 1234 | <other data> |
| 14.00 | 1235 | <other data> |
| 15.00 | 1236 | <other data> |
| 18.00 | 1239 | <other data> |
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.
