Working around Table does not contain OIDs arcpy Error 000339?

Question

I have a script I'm working on that takes a SQL table view and performs a Table to Table (arcpy.TableToTable_conversion)

If I leave out the Where Clause the Table to Table works flawlessly, however if I include the Where Clause it fails throwing Error 000339 "Input does not have OIDs". Typically the way to overcome this error is to copy the table (or table to table) but this is what I'm doing. The table is quite large and takes some time to copy, which is why I'm trying to add the Where Clause.

Is there another way I can get my table copied, filtered by Where Clause, that won't throw the missing OID error?

whereclause = "FeatID IN ('501684', '179845', '173458', '509634')"
arcpy.TableToTable_conversion(WMTR_Pivot, TargetDB, "xxTest_WMTR_Pivot", whereclause, wmtr_fms, "")

Note: FeatID is a text field.

Error:

Traceback (most recent call last):   File "xxAppendMeters.py", line 231, in <module>
    arcpy.TableToTable_conversion(WMTR_Pivot, TargetDB, "xxTest_WMTR_Pivot", whereclause, wmtr_fms, "")   File "C:\Program Files (x86)\ArcGIS\Desktop10.3\ArcPy\arcpy\conversion.py", line 2133, in TableToTable
    raise e ExecuteError: ERROR 000339: Input v_WMTR_Pivot does not have OIDs Failed to execute (TableToTable).

My table view has about 75000 records, and I'm trying to filter that to about 150 records with the Where Clause. I've trimmed that to 4 in the code snippet above.

Table is in SQL Server 2012 Enterprise Geodatabase, on my PC I'm running ArcGIS Desktop 10.3.1

Answer 1

I am getting the same error as you, I have tested your workflow on 10.3.1.

I get the error in both cases, when I have the source view is registered with geodatabase and when it's not. Running the table conversion without supplying the where clause runs fine.

I'm not sure, but it might have something to do with the process of data filtering. If there is no ObjectID field, the ArcGIS won't be able to do the selection.

From the Help page:

The current data format does not have an ObjectID (OID) field, which is required for the operation to successfully execute. Common examples of a data type that does not have an OID field is an x,y event layer or data created in a database using SQL and not registered to ArcSDE or the geodatabase.

The dataset should be first converted to a dataset that will add an OID field. Several tools, including CopyFeatures and CopyRows, can be used to convert the dataset into a supported format that will contain an OID field. For data created using SQL in a database, use the sdelayer/sdetable -o register command to register the table to ArcSDE.

The quick solution to use the in_memory workspace for copying the rows there first:

import arcpy
in_table = r"C:\GIS\Temp\esrigdb.sde\esrigdb.dbo.countries_pop"
out_gdb = r"C:\GIS\Temp\test.gdb"
where = "Country = 'UK'"

in_memory_table = arcpy.CopyRows_management(in_rows=in_table, out_table=r"in_memory\tbl")
arcpy.TableToTable_conversion(in_rows=in_memory_table, out_path=out_gdb, out_name="OutFilteredTbl",where_clause=where)

Copying the rows into the in_memory table won't take much time and lets you workaround the problem.

Answer 2

The Copy Rows to in_memory as suggested by @alex-tereshenkov took the processing time for this part of the script down from 2mins+ to 38 seconds, however I have found an even quicker solution that does what I want.

I ended up using MakeQueryTable which adds an OID and also incorporates the where clause, then the TableToTable runs a lot quicker (down to 18 seconds).

arcpy.MakeQueryTable_management(WMTR_Pivot, "xxQueryTable2", "ADD_VIRTUAL_KEY_FIELD", "", "", whereclause)
arcpy.TableToTable_conversion("xxQueryTable2", TargetDB, "xxTest_WMTR_Pivot", "", wmtr_fms, "")

I tested these by re-creating the same scenario using different methods. Here is my test code, and below that the processing times for each:

whereclause = "WSLAssetID IN ('501684', '179845', '173458', '509634')"

try:
    startTime = datetime.now()
    print "Table to Table"
    arcpy.TableToTable_conversion(WMTR_Pivot, TargetDB, "xxTester1", "", "", "")
    print datetime.now() - startTime
except:
    print "Table to Table didn't work"
    print datetime.now() - startTime

try:
    startTime = datetime.now()
    print "Copy Rows"
    arcpy.CopyRows_management(WMTR_Pivot, "{}\\xxTester2".format(TargetDB))
    print datetime.now() - startTime
except:
    print "Copy Rows didn't work"
    print datetime.now() - startTime

try:
    startTime = datetime.now()
    print "Table to Table using Copy Rows to in_memory (Alex Tereshenkov suggestion)"

    arcpy.CopyRows_management(WMTR_Pivot, r"in_memory\xxTemp")
    arcpy.TableToTable_conversion(r"in_memory\xxTemp", TargetDB, "xxTester3", whereclause, "", "")

    print datetime.now() - startTime
except:
    print "Table to Table using Copy Rows to in_memory didn't work"
    print datetime.now() - startTime

try:
    startTime = datetime.now()
    print "Table to Table using Query Table"

    arcpy.MakeQueryTable_management(WMTR_Pivot, "xxQueryTable2", "ADD_VIRTUAL_KEY_FIELD", "", "", whereclause)
    arcpy.TableToTable_conversion("xxQueryTable2", TargetDB, "xxTester4", "", "", "")

    print datetime.now() - startTime
except:
    print "Table to Table using Query Table didn't work"
    print datetime.now() - startTime

Table to Table
0:02:03.233000
Copy Rows
0:01:53.275000
Table to Table using Copy Rows to in_memory (Alex Tereshenkov suggestion)
0:00:37.078000
Table to Table using Query Table
0:00:18.336000

I hadn't initially tried using the MakeQueryTable as the very next step in my code was also a MakeQueryTable and I had been having difficulty getting data through that which is why I had added the TableToTable before it. That MakeQueryTable step however was a bit more complex so the simplified filter first really helps.