I'm modifying the contents of a table with "fresh data" and make use of the MERGE statement. I'm using something like
merge into MyTable T (<column list>) using with auto name (select <some computed values> from MyOtherTable where -- someConditions order by col4) MOT on T.col2 = MOT.col2 and T.col3 = MOT.col3 and T.col4 = MOT.col4 when matched then skip when not matched then insert;
So I'm obviously only inserting rows. As MyTable has a PK column (as col1) with DEFAULT AUTOINCREMENT, I would like to have the inserted rows in the same order as in the source select statement - i.e. ORDER BY col4. But they are inserted somewhat randomly.
So, in contrast to INSERT ... SELECT ... ORDER BY ...", MERGE seems to ignore the order of the source table, and I don't see a way to specify a further ORDER BY clause.
What can I do?
The MERGE execution plan must left outer join the MOT derived table with 'MyTable T' before any WHEN clause is processed, hence any order imposed in the MOT table maybe lost in this join.
Please try this statement for your specific needs:
insert into myTable T1 (<auto name="" columns="">) (select TOP ALL MOT.* from (select <some computed="" values=""> from MyOtherTable where -- someConditions) MOT left outer join myTable T on T.col2 = MOT.col2 and T.col3 = MOT.col3 and T.col4 = MOT.col4 where T.<pk column=""> IS NULL --- these are the rows in MOT not matching any rows in T order by col4 )