Append works in 2.x but not 3.x

[MichaelKohler]

Moving from older Desktop 2.x script to 3.x in Pro. For some reason, the script fails in 3.x when trying to Append. Two query tables are created with the exact same fields and joins from different layers.  One is created and written to a table, then the other one is created with a query table an appended to the first.Script has been working for years. Tried with and without the brackets.

arcpy.management.Append([lyrNameVN], finalTable,"NO_TEST")

The error is the ever helpful "ERROR 999999: Something unexpected caused the tool to fail"

[RhettZufelt]

Not sure if related, but I run into issues trying to connect to my 10.6.1 eGDB's with arcpy running python 3.  Normally, just tells me that the data doesn't exist.  Have to revert to python 2.7 to do anything with the non-upgraded database.

R_

[MichaelKohler]

Thanks for the comments

I'm using fGDB's. I thought it might be a fGDB version thing because the fGDB comes from an outside source. Unfortunately, the database is the proper version.

[MichaelKohler · Accepted solution]

I figured it out. Gotta love the 999999 error. It should have said the table schemas to not match.

See the definition for the query table below.

    tableList = ['{}\PropertyIndex'.format(src_fgdb),\
                    '{}\SiteLocation'.format(src_fgdb),
                    '{}\Parcels'.format(src_fgdb),\
                    '{}\LegalDescription'.format(src_fgdb),\
                    '{}\PropertyOwnership'.format(src_fgdb)]                    

    fieldList = [["PropertyIndex.OBJECTID",'OBJECTID'],\
                       ["PropertyIndex.ParcelID",'ParcelID'],\
                    ["SiteLocation.StreetDirection",'StreetDirection'],\
                    ["SiteLocation.StreetName",'StreetName'],\
                    ["SiteLocation.StreetWay",'StreetWay'],\
                    ["SiteLocation.UnitNumber",'UnitNumber'],\
                    ["SiteLocation.LocationCity",'LocationCity'],\
                    ["SiteLocation.LocationPostal",'LocationPostal'],\
                    ["SiteLocation.SiteAddress",'SiteAddress'],\
                    ["PropertyOwnership.OwnerFirstName",'OwnerFirstName'],\
                    ["PropertyOwnership.OwnerLastName",'OwnerLastName'],\
                    ["PropertyOwnership.Owner2FirstName",'Owner2FirstName'],\
                    ["PropertyOwnership.Owner2LastName",'Owner2LastName'],\
                    ["PropertyOwnership.Owner3FirstName",'Owner3FirstName'],\
                    ["PropertyOwnership.Owner3LastName",'Owner3LastName'],\
                    ["PropertyOwnership.Street1",'Street1'],\
                    ["PropertyOwnership.Street2",'Street2'],\
                    ["PropertyOwnership.City",'City'],\
                    ["PropertyOwnership.StateProvince",'StateProvince'],\
                    ["PropertyOwnership.PostalCodeFull",'PostalCodeFull'],\
                    ["PropertyOwnership.Country",'Country'],\
                    ["LegalDescription.LegalDescription",'LegalDescription'],\
                    ["PropertyIndex.PropertyID",'PropertyID'],\
                    ["Parcels.XCOORD",'XCOORD'],\
                    ["Parcels.YCOORD",'YCOORD'],\
                    ["SiteLocation.LocationStartNumber",'LocationStartNumber'],\
                    ["Parcels.isConfidential",'isConfidential'],\
                    ["Parcels.PropLatitude",'PropLatitude'],\
                    ["Parcels.PropLongitude",'PropLongitude']]

    whereClause = "PropertyIndex.PropertyID = Parcels.AccountNumber and "\
                    "PropertyIndex.PropertyID = SiteLocation.PropertyID and "\
                    "PropertyIndex.PropertyID = LegalDescription.PropertyID and "\
                    "PropertyIndex.PropertyID = PropertyOwnership.PropertyID and "\
                    "SiteLocation.LocationCity = 'Port Saint Lucie'"


    keyField = "PropertyIndex.OBJECTID"
    lyrNamePN = "SphinxPN" 
    arcpy.management.MakeQueryTable(tableList, lyrNamePN,"USE_KEY_FIELDS", keyField, fieldList, whereClause)        
    arcpy.management.CopyRows(lyrNamePN, finalTable) 

When using using 2.x (Desktop), the field name is just the second name in the field item.

["Parcels.PropLatitude",'PropLatitude'] would be written to the fGDB as PropLatitude.

When using using 3.x (Pro), the field would be written to the fGDB as Parcels_PropLatitude.

So in 3.x, the two feature classes would produce different fields while in 2.x the fields are the same.

see the attached image.

Have to use a field map to resolve.