sql-server-2008 - 您能否指定在 SQL Server 2008 MERGE 语句中插入不匹配行的顺序？

Question

我正在使用 MERGE 语句来基本上执行 UPSERT。对于目标中不存在的行，我希望它们按特定顺序插入。不幸的是，似乎合并语句不支持 ORDER BY 子句。有没有办法在一个声明中做到这一点？请参阅示例以更好地了解我正在尝试做什么:

CREATE TABLE #destination (ident int not null identity(1,1), id int not null,   value int not null)
INSERT INTO #destination (id,value) VALUES (1,50)

CREATE TABLE #source (id int not null, value int not null)
INSERT INTO #source (id,value) VALUES (1,100),(3,300),(2,200)

MERGE #destination d
USING #source s
    ON d.id = s.id
WHEN MATCHED THEN 
    UPDATE 
    SET d.value = s.value
WHEN NOT MATCHED THEN
    INSERT (id,value)
    VALUES (s.id,s.value);

SELECT * FROM #destination ORDER BY ident
/* 
WILL LIKELY SEE:
    1, 1, 100
    2, 3, 300
    3, 2, 200
WANT TO ACHIEVE:
    1, 1, 100
    2, 2, 200
    3, 3, 300
*/

我想这样做的原因是我想为我的代码编写一个单元测试来执行此合并并希望以确定性顺序插入。我知道有很多方法可以解决这个问题，但是如果有一种方法可以订购 MERGE 的插入，那将是最简单的方法。

Answer 1

所以现在这是不可能的。来自 documentation on MERGE ，插入、删除、更新的操作是无序的；但它隐藏在它对 TOP 如何影响它的解释中:

The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions are applied to the remaining joined rows in an unordered fashion. That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. For example, specifying TOP (10) affects 10 rows; of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted and so on.