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

[SumitMishra_016]

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

[SumitMishra_016]

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]

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

[SumitMishra_016]

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.

[VinceAngelo]

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. 

- V

[SumitMishra_016]

Thanks for the reply @VinceAngelo .

Can we use Oracle SQL*Loader using .CTL file like below in cmd

sqlldr username@server/password control=loader.ctl

-- Filename: loader.ctl
LOAD DATA
INFILE 'data.dat' -- Input data file
APPEND INTO TABLE my_table -- Destination table in Oracle

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' -- Specifies CSV format
(
column1, -- First column in Oracle table
column2, -- Second column
column3 -- Third column, etc.
)

[VinceAngelo]

I haven't ever used SQL*Loader, so this should be asked as a different question (and not of me).

Populating new tables, adding all appropriate indexes, then running INSERT/DELETE based on a LEFT OUTER JOIN  mismatch and UPDATE on only the changed rows is the procedure I'm recommending. How you actually implement that is outside the scope of my answer. 

- V