How can I insert data in a particular order using MERGE?

[VolkerBarth]

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?

[VolkerBarth]

FWIW, I'm using MERGE here instead of INSERT ON EXISTING SKIP as the latter would not work because the join condition isn't a primary key - cf. this FAQ...

[VolkerBarth]

Any insights from the SQL/query processing group are still appreciated:)

[MCMartin]

You could insert the col1 explicitly, it is a default autoincrement column but you can still override the values if you like. It won't change the insert order but the primary key values will be equal for identical rows.

[Breck_Carter]

Look at the calendar. Canada has two weeks of summer every year, and this is it. Draw your own conclusions 🙂

[VolkerBarth]

You have only two weeks of summer in Canada? (Yes, I'm aware you're talking about vacancies...)

FWIW, the current "summer" in Germany feels more like late November - cold and rainy:(

@Breck: And we won't talk about the weather in China, right?

[Former Member]

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.
In general, SQL Anywhere ignores any ORDER BY in a nested derived table if, for example, TOP clause is not present.

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 )

[VolkerBarth]

Yes, this will work obviously.

So I assume that the underlying question is answered with

"No, you can't use an ORDER BY clause with MERGE as a top-level CLAUSE."

If this is correct, I'd like to suggest an additional ORDER BY CLAUSE as a MERGE statement enhancement, just like this is possible (at least as a SQL Anywhere vendor extension) with INSERT/UPDATE/DELETE.