Thanks James.
Sorry for the confusion. Model Builder formats the process outs all the same when you export the script as Python. Yes, you're correct I would like to insert rows, but if possible I would like to just update / delete the existing rows where the IDs match to improve efficiency / performance. Since I was not able to figure that out I proceeded with joining the ticket export CSV of daily updates from our work order system to the GIS table to delete only the found set (which would be the current year). Using an append I updated just the current year of tickets.
The FireHydrant_Tickets table resides in the SDE database (MS SQL). It's also setup as a related table to our Fire Hydrants Feature Class. Because the other system we use for processing our Work Orders is not connected to our SQL (design limitations), the only way for us to get the information out is to use a .MER file type (includes column headers – basically a delimited text file). We rename it to .CSV to make it compatible with ArcGIS. We have three export files; EMER and Bagged must be sent via Email to our local county departments, and the Field Tickets are only used to update our information.
The only part of the code that is not working properly is below.
# Start Update Field Ticket Script Part
if os.path.isfile(FMFieldTickets_MER):
# Rename file
os.rename(FMFieldTickets_MER, FMFieldTickets_CSV)
# Process: Make Table View
arcpy.MakeTableView_management(Fire_Hydrant_Tickets_Table, FH_Tickets_Table_Out, "", "", "OBJECTID OBJECTID VISIBLE NONE;pk_FHMF_ID pk_FHMF_ID VISIBLE NONE;fk_FH_ID fk_FH_ID VISIBLE NONE;FHMF_Prepared_Date FHMF_Prepared_Date VISIBLE NONE;FHMF_Type FHMF_Type VISIBLE NONE;FHMF_Flowing_Hydrant_Number FHMF_Flowing_Hydrant_Number VISIBLE NONE;FHMF_Completed_ByName FHMF_Completed_ByName VISIBLE NONE;FHMF_Completion_Date FHMF_Completion_Date VISIBLE NONE;FHMF_Closed_By FHMF_Closed_By VISIBLE NONE;FHMF_Closed_Date FHMF_Closed_Date VISIBLE NONE;z_Status z_Status VISIBLE NONE")
# Process: Add Join
arcpy.AddJoin_management(FH_Tickets_Table_Out, "pk_FHMF_ID", FMFieldTickets_CSV, "pk_FHMF_ID", "KEEP_COMMON")
# Process: Select ALL (Joined)
arcpy.SelectLayerByAttribute_management(FH_Tickets_CSV_Join_Out, "NEW_SELECTION", "")
# Process: Delete Rows
arcpy.DeleteRows_management(FH_Join_Output)
# Process: Remove Join
arcpy.RemoveJoin_management(FH_Delete_Matches, "")
# Process: Append
arcpy.Append_management(FMFieldTickets_CSV, FH_Tickets_Table_Out, "NO_TEST", "pk_FHMF_ID \"Ticket #\" true true false 4 Long 0 10 ,First,#,"+ FMFieldTickets_CSV +",pk_FHMF_ID,-1,-1;fk_FH_ID \"Fire Hydrant #\" true true false 4 Long 0 10 ,First,#,"+ FMFieldTickets_CSV +",fk_FH_ID,-1,-1;FHMF_Prepared_Date \"Prepared Date\" true true false 36 Date 0 0 ,First,#,"+ FMFieldTickets_CSV +",FHMF_Prepared_Date,-1,-1;FHMF_Type \"Type\" true true false 255 Text 0 0 ,First,#,"+ FMFieldTickets_CSV +",FHMF_Type,-1,-1;FHMF_Flowing_Hydrant_Number \"Flowing Hydrant #\" true true false 4 Long 0 10 ,First,#,"+ FMFieldTickets_CSV +",FHMF_Flowing_Hydrant_Number,-1,-1;FHMF_Completed_ByName \"Completed By\" true true false 255 Text 0 0 ,First,#,"+ FMFieldTickets_CSV +",FHMF_Completed_ByName,-1,-1;FHMF_Completion_Date \"Completion Date\" true true false 36 Date 0 0 ,First,#,"+ FMFieldTickets_CSV +",FHMF_Completion_Date,-1,-1;FHMF_Closed_By \"Closed By\" true true false 255 Text 0 0 ,First,#,"+ FMFieldTickets_CSV +",FHMF_Closed_By,-1,-1;FHMF_Closed_Date \"Closed Date\" true true false 36 Date 0 0 ,First,#,"+ FMFieldTickets_CSV +",FHMF_Closed_Date,-1,-1;z_Status \"Status\" true true false 255 Text 0 0 ,First,#,"+ FMFieldTickets_CSV +",z_Status,-1,-1", "")
#arcpy.Append_management(FMFieldTickets_CSV, FH_Tickets_Table_Out, "NO_TEST","","")
# Process: Reconcile Versions
arcpy.ReconcileVersions_management(GISSQL_Main__sdeadmin__sde, "ALL_VERSIONS", "dbo.DEFAULT", "DBO.QA_BASE", "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "KEEP_VERSION", Reconcile_Versions_Log)
# Remove CSV
os.remove(FMFieldTickets_CSV)
