Can you specify an order unmatched rows are inserted in a SQL Server 2008 MERGE Statement?

I am using a MERGE statement to basically do an UPSERT. For the rows that are not present in the destination, I would like them inserted in a certain order. Unfortunately, it seems the ORDER BY clause isn't supported with a merge statement. Is there any way do this in one statement? See example for better idea of what I am trying to do:

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
*/

The reason I want to do this is I would like to write a unit test for my code that performs this merge and want the insertions in a deterministic order. I know there 开发者_如何学JAVAare ways to get around this, but if there is a way to order the insertion of a MERGE it would be the easiest.

---

So right now this is not possible. From the documentation on MERGE, the operation of inserts, deletes, and updates is unordered; but it was burried in it's explanation of how TOP affects it:

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.

---

Not sure if this would be acceptable in your case, but you could use SET IDENTITY_INSERT to override the identity column and guarantee your order that way.

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)

SET IDENTITY_INSERT #destination ON

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 (ident,id,value)
    VALUES (s.id, s.id, s.value);

SET IDENTITY_INSERT #destination OFF

SELECT * FROM #destination ORDER BY ident