Please suggest a way to append data from GDB to Oracle SDE and truncate if data in a field of matches with source using SDK.
"TRUNCATE" is an unlogged removal process, so you probably mean "UPDATE" or "DELETE". "Append" is generally an INSERT. Your update criteria isn't very clear, so it's not easy to make a specific recommendation.
I've had good luck using FeatureClassToFeatureClass or FeatureClassToGeodatabase into a temporary table in the database, then using SQL to UPDATE changed rows, INSERT new rows, and DELETE removed rows. This was not in a versioned feature class, so I didn't have to deal with versioned views.
arcpy.ArcSDESQLExecute() can be used to issue the SQL from ArcPy. Not sure what the Pro SDK equivalent would be.
- V
Thanks for the response @VinceAngelo ,
I have to INSERT new rows from GDB to SDE but first check if data is already present in the SDE. Is there any examples for INSERT new rows from GDB to SDE in arcpy.ArcSDESQLExecute()?
While possible, that's not what I was recommending. Using a binary loader is the fastest way to get all features into a staging table. Then it's a simple join to identify features not present:
INSERT INTO table1 ( {long_list_of_columns}) SELECT {long_list_of_columns_prefixed_with_"t."} FROM temptable t LEFT OUTER JOIN table1 p ON p.keyfield = t.keyfield WHERE p.keyfield IS NULL
You would, of course, need an index on the keyfield (or keyfields) in table1, and have a mechanism for generating a reliable objectid value (this was dirt easy with a serial column in PostgreSQL, but there should be a function you can use with Oracle).
Detecting the changed rows is a similar UPDATE statement. Identifying the rows to mark for deletion would flip the table order, and require an index on temptable(keyfield).
I've found that it's an order or magnitude or two faster to load the parallel table, then manifest deltas in a single commit, than to deal with variable duration downtime on the data during a TRUNCATE/INSERT/REINDEX outage.
Hi @VinceAngelo ,How to use the geodatabase file in ArcGIS pro in binary loader?Could you please explain step by step?I have the data in local File Geodatabase and then I have same schema in Oracle Database. I want to know the fastest way to load the data from local fileGDB to EnterpriseGDB in Oracle. Also, I have Annoatation Feature classes in the geodatabase.
I'm not real good with the step-by-step thing, and am forbidden by NDA to give explicit details . All I can say is that I used FeatureClassToFeatureClass and TableToTable to populate a few dozen tables as an interim data change set into a staging schema within the database, using a common randomly generated name prefix, then executed many tens of thousands of SQL statements (via arcpy.ArcSDESQLExecute) to manifest the change. The load took twenty minutes, and the base table population via SQL ten more, then the hierarchical data propagation took another twenty. 120+ million rows were processed and the services publishing the data remained live through the process.
I haven't attempted anything with annotation.
Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.
