ETL Process for SQL Tables

[BrianLeroux]

My DB Admins are not familiar with ArcSDE so I am trying to find guidance on proper ways to load data into SQL server tables that are registered with SDE. The data are geocoded points from another non SDE database and have a geometry filed for location data. After I register the table it is adding a new column called [GDB_GEOMATTR_DATA]. 

So the admins have a nightly process to truncate ,insert all the rows, and finally update to fill geometry column. This is done purely in SQL and is not using ArcGIS to load any data. That will leave the [GDB_GEOMATTR_DATA] column empty. So my question is what is the proper ETL strategy with a registered table and can we perform the ETL without needing ArcGIS or ArcPy?

[VinceAngelo]

Brian --

There are potential issues to managing records in a registered table in this manner. The easiest solution is to just leave the feature class unregistered, and access it as a Query Layer.  But make sure you recluster the table, since doing this sort of "load, then update geometry" process thoroughly fragments the table. 

Truncate and reload is not a very efficient process, and leaves any service looking at the table during that transition somewhat bereft. I prefer to do change detection, and only INSERT new records, UPDATE changed records, and DELETE removed ones. I'm doing this daily with tens of millions of rows and hundreds of thousands of potential changes, in just minutes over the time it takes to read the new records.

- V