We have been updating/inserting/deleting using append tool for the data on regular basis from non spatial database (weekly).
I have looked at different options to do,
1. Append tool : I tried this tool to update the data on regular basis ( which is taking longer time then expected), sometimes, Update feature is also not working based on primary key. When insert 68,000 records , it took me just 9 min which is more time to insert and then tried to run same tool with update option enabled, it took me almost 1:09:00 . it appends all records instead of update.
2. Using Versioned views using SQL queries (MERGE) : it is much faster compared to append geoprocessing tool. i was getting an error while running merge sql query against _evw (versioned and archived), "You may not update this view on an archiving table in the DEFAULT version." . I read in online documentation, we need to set version before running this tool ( it may be child /default). Anyways, i will setup indexes to boost the performance of tool. When we are updating this, need to pass globleid manually. Can we set in SQL side to automate creation of this GUID?
Append only works for UPSERT operation and it does not work for delete operation. we need to write another function to delete unmatched records.
With this, OBJECTID value keep on increasing to large number on regular data updates and we can't use truncate table to reset objectid on versioned feature classes
I would like to proceed best approach either sql side job or geoprocessing tool . Could you please anyone guide us to move forward
