title: Can you specify an order unmatched rows are inserted in a SQL Server 2008 MERGE Statement?
body: <p>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:</p>
  
  <pre><code>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
  */
  </code></pre>
  
  <p>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 are ways to get around this, but if there is a way to order the insertion of a MERGE it would be the easiest.</p>