Overwrite data from existing feature class in GDB to feature class in Oracle SDE using SDK or arcpy

[sumalain1]

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. 

[VinceAngelo]

"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

[sumalain1]

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()?

[VinceAngelo · Accepted solution]

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.

- V