topic Re: delete/append order of operation with relationships. in Geodatabase Questions

delete/append order of operation with relationships.

clt_cabq — Tue, 06 Feb 2024 15:35:44 GMT

Is there an order of operations requirement when updating a feature class and related table - such as, do you have to update a feature class first before the related table? I have a geodatabase (sql server backend) with a feature class and a related (1:M) table that I update once a month. I run (manually) a delete/append operation against both of these items after I've updated data in a staging database. I use truncate table to delete the existing rows in the gdb table. I always inspect the table to make sure all records match the source table, especially the field that is used to relate to the feature class. I've found that I can inspect the table and find all values in the related field have updated, but then after I run the delete/append on the feature class, it appears those related values in the table 'disappear' everytime. For instance I ran this update today and had 0 null values in the relate field before updating the feature class but afterwards there were about 96K out of 98K records with null values in the related field in the table. Typically I am running the truncate/append operation first, then the delete/append on the feature class, and if i redo the truncate/append process a second time, everything comes through with no issues.

Re: delete/append order of operation with relationships.

MarceloMarques — Tue, 06 Feb 2024 17:20:03 GMT

You should

1. truncate the related table

2. then truncate the featureclass

3. then reload the data

4. load the featureclass

5. load the related table

If the geodatabase relationship class 1:M is based on GUI values then nothing to worry.

If you are using ObjectID then you might need to preserve the original objectid value in the featureclass as a new field e.g orig_oid and preserve the related table foreign key value as a new field as well e.g orig_oid_fk, then you can repopulate the related table foreign key value with the new objectid of the featureclass using the original ObjectID values.

I hope this helps.

Re: delete/append order of operation with relationships.

clt_cabq — Tue, 06 Feb 2024 20:07:03 GMT

@MarceloMarques thanks! I'll try this approach next time around. The related field is neither the GUID or Object ID but a unique parcel value.

Re: delete/append order of operation with relationships.

clt_cabq — Wed, 14 Feb 2024 14:35:38 GMT

@danaharre I'm not sure what you mean by wrapping the update operations in a transaction, can you give me an example?

Re: delete/append order of operation with relationships.

MarceloMarques — Wed, 14 Feb 2024 16:40:20 GMT

@clt_cabq - RDBMS Transactions, see SQL Server example
BEGIN TRANSACTION (Transact-SQL) - SQL Server | Microsoft Learn

Oracle, SQL Server, PostgreSQL support Transactions.

And if you are using ArcGIS Pro Python ArcPy or the ArcGIS Pro .NET SDK then the API also has methods to work with transactions.

Arcpy editor transaction rollback - Esri Community

Re: delete/append order of operation with relationships.

clt_cabq — Wed, 14 Feb 2024 17:23:26 GMT

Thanks @MarceloMarques, currently I'm running this simply in a manual set of procedures, but one of my upcoming tasks is to automate this (using python) as the last step of several procedures, so these references really help.

Re: delete/append order of operation with relationships.

clt_cabq — Mon, 04 Mar 2024 15:16:11 GMT

Just reporting back - i followed this sequence in my manual processing of these data this morning and it worked perfectly well. Next steps are to automate this process so the comments about wrapping this into a transaction will be helpful.