1

I am having an issue with this and think there is a simple solution.

I have a large table, upwards of 500,000 records and need to add new records to it daily. Occasionally a new record will just be an update to a field or two and I want the record from the new table to replace the old record with the same Unique ID.

I have tried appending the new (smaller) table to my original (large) table and then running a delete identical, however sometimes the old row is kept and the new record is deleted.

I would like to be able to compare the tables based on Unique ID, delete all identical ID's in my original table and then append the new table.

Either in ModelBuilder or python as this needs to be done daily.

  • I have tried appending the new (smaller) table to my original (large) table and then running a delete identical, however sometimes the old row is kept and the new record is deleted. – S.P. Jul 20 '16 at 15:14
1

I do this all the time. In my particular case I maintain a one-off database that needs to be synchronized with the actively maintained data, but it cannot be overwritten by the original database, since I maintain additional fields in my copy that are not in the original.

The fastest approach for this scenario requires the use of dictionaries and cursors. Here is a script I run nightly. It is a databases with over 900,000 records and new records are inserted into it and existing records are updated each night. The script takes about 20 minutes to run. The embedded cursor approach takes days to process that many records. I will try to get it down to the critical code, but it is a specific example, so I don't want to break it by pairing down too much. The unique ID field is a string field called LMS_KEY (certain parts of the code need to change if your key is numbers) and four dictionaries are built, the first of which simply holds all unique keys in the actively updated source data (valueDict), the second holds all unique keys that are already in the stored database along with the set of fields I want to automatically update (newDict), the third holds all new keys that need to be inserted into the stored database (newDict2), and the fourth dictionary holds the full record associated with each key that actually gets inserted into the stored database (insertDict):

from time import strftime
import datetime

print( "Start script: " + strftime("%Y-%m-%d %H:%M:%S")  )

import arcpy  

Trans_Connection_to_SQL_Server_sde = r"C:\Users\RFAIRHUR\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\Trans Connection to SQL Server.sde"

sourceFC = r"\\agency\AgencyDFS\Tran\FILES\GISData\rfairhur\Layers\Plus_Conversion_Data\Plus_Conversion_Data.gdb\LMSACTIVITIES_BUILDING"  

sourceFieldsList = ["LMS_KEY", "STATUS"]  

# Use list comprehension to build a dictionary from a da SearchCursor  
valueDict = {r[0]:r[1] for r in arcpy.da.SearchCursor(sourceFC, sourceFieldsList, "LMS_KEY > ' '")}  

print( "Finished Reading Data Source 1: " + strftime("%Y-%m-%d %H:%M:%S"))

sourceFC = r"C:\Users\RFAIRHUR\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\Connection to PUB.sde\GDB_PUB.TLMA.LMSACTIVITIES_BUILDINGSAFETY"  

sourceFieldsList = ["LMS_KEY", "STATUS", "APPLIED_DATE", "APPROVED_DATE", "COMPLETED_DATE", "EXPIRED_DATE"]  

newDict = {}
count = 0
newDict2 = {}
count2 = 0
# Use list comprehension to build a dictionary from a da SearchCursor  
with arcpy.da.SearchCursor(sourceFC, sourceFieldsList, "LMS_KEY > ' '") as sourceRows:  
    for sourceRow in sourceRows:  
        # store the Join value of the row being updated in a keyValue variable  
        keyValue = sourceRow[0]  
         # verify that the keyValue is in the Dictionary  
        if keyValue in valueDict:  
             # transfer the value stored under the keyValue from the dictionary to the updated field.  
            newDict[keyValue] = (sourceRow[1], sourceRow[2], sourceRow[3], sourceRow[4], sourceRow[5])
            count += 1
        else:
            newDict2[keyValue] = sourceRow[1]
            count2 += 1

print(("Matched {} Cases").format(count))
print(("Unmatched {} Cases").format(count2))
print(newDict2.keys())
print( "Finished Reading Data Source 2: " + strftime("%Y-%m-%d %H:%M:%S"))

updateFC = r"\\agency\AgencyDFS\Tran\FILES\GISData\rfairhur\Layers\Plus_Conversion_Data\Plus_Conversion_Data.gdb\LMSACTIVITIES_BUILDING"
#updateFC = r"C:\Users\RFAIRHUR\Documents\ArcGIS\Default.gdb\BRS_NEW_RESHAPE"  

updateFieldsList = ["LMS_KEY", "STATUS", "APPLIED_DATE", "APPROVED_DATE", "COMPLETED_DATE", "EXPIRED_DATE"]  

with arcpy.da.UpdateCursor(updateFC, updateFieldsList, "LMS_KEY > ' '") as updateRows:  
#with arcpy.da.UpdateCursor(updateFC, updateFieldsList, "NEW_APN > '9631' and NEW_APN < '9632'") as updateRows:  
    for updateRow in updateRows:  
        # store the Join value of the row being updated in a keyValue variable  
        keyValue = updateRow[0]  
         # verify that the keyValue is in the Dictionary  
        if keyValue in newDict:  
             # transfer the value stored under the keyValue from the dictionary to the updated field.  
            updateRow[1] = newDict[keyValue][0].strip()
            updateRow[2] = newDict[keyValue][1]
            updateRow[3] = newDict[keyValue][2]
            updateRow[4] = newDict[keyValue][3]
            updateRow[5] = newDict[keyValue][4]
            updateRows.updateRow(updateRow)  

del valueDict
print( "Updated the Status of the Cases: " + strftime("%Y-%m-%d %H:%M:%S"))

if count2 > 0:
    updateFieldsList = ["OID@", "LMS_KEY", "CASENAME", "APN", "CASE_TYPE","STATUS","DEPARTMENT","APPLIED_DATE","APPROVED_DATE","COMPLETED_DATE","EXPIRED_DATE","CASE_TYPE_DESC","CASE_DESC", "CONSTRUCTION", "HOUSE_COUNT","FCC_CODE","OCCUPANCY","TYPE_RD","LOCATION","SHAPE@"]
    insertDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, updateFieldsList, "LMS_KEY IN ('{}')".format("','".join(newDict2.keys())))}
    updateFieldsList = ["LMS_KEY", "CASENAME", "APN", "CASE_TYPE","STATUS","DEPARTMENT","APPLIED_DATE","APPROVED_DATE","COMPLETED_DATE","EXPIRED_DATE","CASE_TYPE_DESC","CASE_DESC", "CONSTRUCTION", "HOUSE_COUNT","FCC_CODE","OCCUPANCY","TYPE_RD","LOCATION","SHAPE@"]
    icursor = arcpy.da.InsertCursor(updateFC, updateFieldsList)
    for key in insertDict.keys():
        icursor.insertRow(insertDict[key])
    del icursor
    del insertDict
    print("Inserted Records: " + strftime("%Y-%m-%d %H:%M:%S"))
0

The following possible solution is merely a reflex-reaction to the situation described in your question. I am concerned that this solution combined with the amount of records involved will present an issue in terms of the amount of computation necessary to complete the task. The following would essentially pass through your large table (500K records), and for each record in your large table, a comparison would be run against each row in the smaller table (incoming records), deleting original records which will be replaced. Then, a second operation inserts the incoming records into the original table. This approach could perform something like 500K * # of incoming records comparisons in the first pass alone, not counting the overhead for setting up the cursors. Obviously, this is problematic. The following assumes a file geodatabase environment:

import arcpy

path_to_original_table = "C:\\data\\filegdb.gdb\\original_table"
path_to_incoming_table = "C:\\data\\filegdb.gdb\\incoming_table"
incoming_row_oid_list = []

with arcpy.da.UpdateCursor(path_to_original_table, ['OID@']) as outer_cursor:
    for outer_row in outer_cursor:
        original_oid = outer_row[0]
        with arcpy.da.SearchCursor(path_to_incoming_table, ['*']) as inner_cursor:
            for incoming_row in inner_cursor:
                incoming_oid = incoming_row[0] # ...we are hoping field 0 is OID
                if incoming_oid == original_oid:
                    outer_cursor.deleteRow(outer_row)
                    incoming_row_oid_list.append(incoming_oid)

with arcpy.da.InsertCursor(path_to_original_table, ['OID@']) as outer_cursor:
    for outer_row in outer_cursor:
        with arcpy.da.SearchCursor(path_to_incoming_table, ['*']) as inner_cursor:
            for incoming_row in inner_cursor:
                for item in incoming_row_oid_list:
                    if item == incoming_row[0]: # ...we are hoping field 0 is OID
                        outer_cursor.insertRow(incoming_row)

Best Luck. Is there any chance you could perform this work in a regular DBMS?

  • The issue is that I need this to run daily to publish as a map service that needs to use domains. I was originally just doing it through a SQL database but in order to use domains I have to create a map service that uses a geodatabase. If there was a way of just replacing the table daily through a script then that would work as well. – S.P. Jul 20 '16 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.