Not sure where to post this; can't seem to find an appropriate Place, and I have searched all over trying to find an answer
I have an SQL EXPRESS 2012 instance that has been enabled for geodatabases and have 2 geodatabases in it that work fine. In the same instance, I recently added another database (non-spatial) that I created in SQL Server Management Studio for which I have made a Database Connection. I can add a table from this non-spatial database to the map and I can join geodatabase tables to it, but I cannot edit it. Is there something wrong in the way I have set it up, or is this the way things work?
If it is non-editable in ArcGIS, what would be the best way to handle the scenario where data from a geodatabase table is needed to up-date the non-spatial database table, and vice versa? (The non-spatial database's primary function has nothing to do with ArcGIS, but I would like to be able to work with one particular table from it.)
Simple SQL-Server databases can be edited using ODBC or a Microsoft API, but not with the same code that edits versioned geodatabases.
It's actually very inefficient to act across databases, which is part of why doing so isn't supported by Esri. But you could likely create triggers for some update processes and work out batch update scripts for others.
You cannot use ArcMap directly to edit attribute values in tables in a database.
The below link describes what you can do with database data in ArcGIS at 10.1 and 10.2.x.
A quick tour of working with databases in ArcGIS:
However starting from 10.2.x:
If you have ArcGIS for Server, you can publish a feature service that contains data from an IBM DB2 (Linux, Unix, or Windows), IBM Informix, Microsoft SQL Server, Oracle, or PostgreSQL database. You can set what type of edits can be made to the data through the feature service, and give people access to the feature service so they can edit the data. See What is a feature service? and related topics for more information.
I agree with the comment about putting the table in an editable feature service. That should work if you include an identity column to serve as the ObjectID. This would be a good approach if your uses needed row-level insert, update, delete actions via some sort of UI.
But if you just want to push data from an SDE feature class into business tables in some other database, you can do this with a cross-database update query. You could schedule this as a SQL Server job or if you wanted it on demand, use pyodbc in a python tool and publish it as a geoprocessing service.
Thanks Vince, Asrujit, and Ted,
Each of your answers has been helpful to me. Not sure yet which way we'll go, a feature service or a python tool. I'll have to get more information from those who are needing to do this. Personally, I'm leaning in the direction of a Python tool; guess its time to try out pyodbc.