I want to set a column as an integer primary key separate from what Esri maintains. This is so it can exist in non-spatial tables and the features retain the key as they exist across other tables.
The problem is, if a field is set to Identity or Generated column, it does not allow direct inserts/updates to write to the field. I want these values populated at the time of appending to the layer via ArcGIS Pro.
The way the append tool works is it generates a prepared statement listing all the fields and all the values in the destination table -- even if they aren't referenced/mapped during the append. This prevents the load from working because it is trying to insert/update on the Identity field instead of letting it do its own thing.
I am looking at DB triggers to do this but it doesn't seem like a clean approach. I've also thought about running the incrementing integer field on the table in pro but that only works on unregistered layers. Any ideas?
STATEMENT: INSERT INTO poi_gdb.poi.poi_raw (objectid, name, primary_category, secondary_category, osm_unique_id, shape, poi_id, processed_timestamp) VALUES ( $1, $2, $3, $4, $5, $6 , $7, $8)
