1) Have you considered using MobiLink to synchronize the data between your SQL Anywhere and MS SQL Server database? It's a pretty proven technology IMHO, but my opinion is quite biased.

https://help.sap.com/docs/SAP_SQL_Anywhere/a09ffd5a93ff477cbcf9d9b1aa4d0801/9462b226f4ae48d1a62b16f57209dfdd.html?locale=en-US

2) With respect to the query you've written if you're not going to use MobiLink, can you provide some more context?

• Is this code written in an update trigger?
• Which tables are local tables and which tables are defined as proxy tables?

Thanks, Reg

FWIW, if you do not want to use MobiLink, you can also "update" remote data via proxy tables via one of these two options. Say, the local SQL Anywhere table is called LT ("Local table") and the proxy table to the MS SQL Database is called RT ("remote table"), both just having one PK column ID and an arbitrary column "Col".

• If there are no data modifications made in MS SQL, and there are no foreign keys in MS SQL on the according table, and FT is supposed to contain all rows of LT, you might simply delete data from FT and then re-insert the contents from LT into FT. This approach does not require an updatable join between local and proxy tables. (Of course, if this is a huge table, that simple approach might have negative performance implications.)

• Otherwise, you can update FT via single-row UPDATE statements based on the contents of a modified row from LT, again omiting an an updatable join.
  We have used that approach with the following (untested!) scheme that is based on a for loop over the rows of LT that are different from those in FT:

   begin
      for myLoop as myCrs insensitive cursor for
         select LT.ID as MyID, LT.Col as MyCol from LT
         except
         select ID, Col from FT
         order by LT.ID
      for read only    
      do
         if (select count(*) from FT where ID = MyID) = 0 then
            insert FT (ID, Col) values (MyID, MyCol);
         else
             update FT set Col = MyCol where ID = MyID;
         end if;
      end for;
   end;

If you have to apply DELETEs, too, you could use a similar approach to select those rows on FT whose IDs do not exist in LT anymore, and delete those rows in FT accordingly.

Basically, that's a "home-brewn" MobiLink approach. It might be worthwhile for the replicaton of one or a few tables but I would highly recommend MobiLink otherwise.