Compare two tables and copy over missing records

DavidBuehler — Wed, 28 Jul 2021 16:28:34 GMT

Try this again -

This time it is do an end around the versioning issue with a Survey123 table (backend in SDE). To my understanding based on this article Survey123 based feature services (tables) in SDE cannot be versioned- https://doc.arcgis.com/en/survey123/desktop/create-surveys/survey123withexistingfeatureservices.htm#:~:text=The%20data%20cannot%20be%20versioned,before%20enabling%20attachments%20on%20either.

Let's say I have a related table to versioned sanitary lines, the related table has to be versioned as well. Since Survey123 cannot handle versioned data, I cannot push to that table directly. I think I need one table to service the Survey123 form, and the the other to fulfill the relationship. My thinking was compare the two tables and move the new records from Survey123 to the related table, based on globalIDs.

Here is what I have come up with thus far. Not sure if I have to do an edit session (I think I do since one of them is versioned). I also get this error: Message File Name Line Position
Traceback
<module> C:\Users\Dbuehler\Desktop\scripts\TryUpdatingWW.py 74
RuntimeError: Underlying DBMS error [[Microsoft][ODBC Driver 17 for SQL Server]Syntax error, permission violation, or other nonspecific error] [COMGIS3.GIS.SanCleanings_Test]

Any help will be much appreciated,

import arcpy import sys import os print "Looping through and adding and deleting" #Looping through each feature class and building a list of comparsions. Then adds or deletes them to the SDE feature class. def createMatchingFieldList(fc1, fc2): lst1 = [fld.name for fld in arcpy.ListFields(fc1)] lst2 = [fld.name for fld in arcpy.ListFields(fc2)] return list(set(lst1) & set(lst2)) def createWhereClause(fc, fld_name, value): if len(arcpy.ListFields(fc, fld_name)) == 1: fld = arcpy.ListFields(fc, fld_name)[0] if fld.type == "String": where = "{0} = '{1}'".format(arcpy.AddFieldDelimiters(fc, fld_name), value) else: where = "{0} = {1}".format(arcpy.AddFieldDelimiters(fc, fld_name), value) return where def getPrimaryFieldValues(fc, field): return [r[0] for r in arcpy.da.SearchCursor(fc, [field])] def getSelectCursor(fc, flds, whereClause): return arcpy.da.SearchCursor(fc, flds, whereClause) def diff(a, b): return list(set(a) - set(b)) #source = arcpy.GetParameterAsText(0) #destination = arcpy.GetParameterAsText(1) #fieldName = arcpy.GetParameterAsText(2) source = 'Database Connections/GIS Editor to COMGIS3.sde/COMGIS3.GIS.SanCleanings_Test' destination = 'Database Connections/GIS Editor to COMGIS3.sde/COMGIS3.GIS.SanCleanings_Test2' fieldName = "globalid" # Start an edit session. Must provide the workspace. edit = arcpy.da.Editor(r'C:\Users\Dbuehler\AppData\Roaming\ESRI\Desktop10.7\ArcCatalog\GIS Editor to COMGIS3.sde') # Edit session is started without an undo/redo stack for versioned data # (for second argument, use False for unversioned data) edit.startEditing(False,False) # Start an edit operation edit.startOperation() # create a list of field names which are in both featureclasses flds = createMatchingFieldList(source, destination) sourceValues = getPrimaryFieldValues(source, fieldName) destinationValues = getPrimaryFieldValues(destination, fieldName) additions = diff(sourceValues, destinationValues) #deletions = diff(destinationValues, sourceValues) with arcpy.da.InsertCursor(destination, flds) as insertCursor: for a in additions: where = createWhereClause(source, fieldName, a) insertRows = getSelectCursor(source, flds, where) for r in insertRows: insertCursor.insertRow(r) #for d in deletions: # where = createWhereClause(destination, fieldName, d) # with arcpy.da.UpdateCursor(destination, flds, where) as deleteCursor: # for d in deleteCursor: # deleteCursor.deleteRow() # Stop the edit operation. edit.stopOperation() # Stop the edit session and save the changes edit.stopEditing(True) print "Success"

topic Compare two tables and copy over missing records in Python Questions

Compare two tables and copy over missing records