Update feature class in an enterprise GDB using SSMS?

[StephenM]

I have a point feature class with over a million records in an enterprise geodatabase (using Microsoft SQL Server). I have a Portal feature layer running off this data. When I need to update the data, I use ArcGIS Pro to delete the feature class and recreate it (either all at once or in chunks using append). When I'm in the office this takes about 3 hours to complete. Now working remotely, updating this feature class takes around 16 hours.

Working with tables is quick in SQL Server Management Studio, so I'm wondering if I can update the table and create the geometry there rather than in ArcGIS Pro / ArcMap / ArcCatalog. I've seen some documentation that suggests this may be possible...

Rules for creating spatial tables to be used with ArcGIS—Help | Documentation 

SQL Server spatial types and ArcGIS—Manage geodatabases in SQL Server | Documentation 

Register a table or view with the geodatabase—Geodatabases | Documentation 

I've had some success, but haven't quite gotten what I want. Using lat/long, I added geometry to a table in SSMS (and added an ObjectID and a spatial index) and was able to visualize the data in the associated feature layer in the Portal, but when I tried to filter the layer in a dashboard I got an error: "Geodatabase error: DBMS does NOT support this function".

Is it possible to update a feature class in an enterprise geodatabase by creating the geometry in SSMS, rather than using ArcGIS desktop software? If so, what's the correct workflow?

[JoshuaBixby]

If you are creating a table outside of ArcGIS with the intent to register it in an enterprise geodatabase, I would not create a key named ObjectID since that is a default/reserved name in ArcGIS.

Although you link to the documentation for registering a table with the geodatabase, are you actually doing so?  It might be best to lay out your steps you have taken to create the table, register it, and then view it.

[StephenM · Accepted solution]

Thanks Joshua! I forgot to mention that I had tried registering, but ran into errors when doing so. I just ran through the process again and I think my issue was that the shape field was set to be "not null". I set the shape field to be nullable and was able to register the table. Now that the table is registered the associated feature layer works fine in a dashboard. I wasn't sure if I needed to register the table, but it looks like that was the key.

The steps I took were:

1. Used SQL Server Management Studio to add and populate a geometry field in the table based on lat/long
2. Used the Register with Geodatabase tool to register the table with the geodatabase, allowing the tool to create an ObjectID and using the geometry field for the shape
3. Added spatial index

Is that about right?

[JoshuaBixby]

Glad you got it working, and yep, those steps should continue to work for you. 

Since I am not a moderator in the Geodatabase space, I can't close out this thread.  Can you either mark a reply as correct or mark the question assumed answered to close it out.  Thanks.

[ParhatM]

Hey Stephen, I'm trying to accomplish similar to what you did. I have a SQL Server based ArcGIS Geodatabase and I need to automate data update for a handful of tables (Spatial and Non-Spatial). Currently what I have done was that I was able to create tables with geometry columns and they somehow plotted correctly on the map. But as my next step, and as you suggested above, I'll have to register some tables too. Did you have to register those tables manually every time with GeoDatabase when you have to drop and recreate them (for data update, it's easier for me to recreate as opposed to update/append) ? Or were you able to automate the registration with some scripts (ArcPy, etc.)? Please give me some clues on how you implemented at the end. Thanks.

[DougGreen]

@ParhatM ,

I think that there's a simpler method to doing this if the schema of the feature classes and tables don't change every time. I'd say you could register the FC/Table once and then just use T-SQL to swap out the data in it on a regular basis through a SQL Agent Job or a script that executes a stored procedure in the database. I have described the process we use for this exact situation in this post. It allows our Feature Classes to be used as a standard EGDB feature class except that it is not to be edited through GIS Software. I have many of these and I don't have to spend any time updating them until someone requests a change to the field definitions.

[ParhatM]

Thanks. I'll definitely try your approach first.