ArcGIS Pro won't export unique identifier of SQL View table to gdb table

[AndreaB_]

Hello all,

I found my question asked here on gis stack exchange https://gis.stackexchange.com/questions/458196/arcgis-pro-wont-export-unique-identifier-of-view-tabl... but I am not on the gis stack exchange so I wanted to bring it to the Esri community. I am having the same problem as the poster there - I have a SQL view with a unique identifier. When I export the view to a table in a geodatabase the unique identifier does not export. In the Fields view it sees it as data type "Object ID". I'm using Pro 3.2.4 and I also tried it in 3.1.5. 

Assuming this is a bug?

I tried the workaround suggested by the one answer: "creating a new table in the GDB with an imported schema matching the SQL table, then appended the SQL table records to the new table." But all I got was a field with 0s in it.

Does anyone have a way to get this to work?

Thank you!

[AndreaB_]

Welp, to answer my own question - I used Table to Excel and then Excel to Table and it worked. Excel to the rescue. Esri - what's the issue here?

[SSWoodward]

Thanks for the question @AndreaB_ 

Can you share a bit more information about your workflow so I can help you get to an answer? When you say "When I export the view to a table in a geodatabase the unique identifier does not export."  What exactly do you mean?  Do you mean that the unique id values are not maintained in the exported table? Or that the table lacks a unique ID field?

[AndreaB_]

Thanks @SSWoodward. Sure thing. I right click on the SQL view in my ArcGIS Pro contents list and Data - Export Table. 

Or I use the Export Table from the geoprocessing pane - should be the same tool.

I export it to the project gdb. 

When I look at the new exported table, the field that was the unique identifier is not there - it's replaced with Esri's OBJECTID. 

SQL view:

GDB table after exported from the SQL view in ArcGIS Pro:

ArcGIS Pro replaced the SiteID with a new OBJECTID. The OBJECTID is not equal to the numbers in the SiteID column. I think it does this because the SiteID is seen as Data Type Object ID in the Fields pane. See Fields Pane of SQL view:

But I need that SiteID number. 

Thank you,

Andrea

[SSWoodward]

Thanks for clarifying.  

This is the expected behavior.  When you make a brand new table, like what happens in an export operation, the unique ID will be generated for that table. OBJECTIDs do not maintain through these types of operations.  Check out this support article on the topic.

Unable to maintain OBJECTID when exporting 

You can also use the field map in the Export Features GP tool to map your SiteID to a new SiteID field in the exported table.  

[AndreaB_]

ah ok. So since this is a SQL view to a gdb table, I wouldn't think of OBJECTID being overridden like that. I know that the field called OBJECTID in a shp or feature class is not maintained on export.

But since this field is called SiteID and only called an "OBJECTID" behind the scenes in the Field properties - I wouldn't expect it. But I get your point. It would be nice if the software let you know what it's doing or didn't do this for a SQL view.

In any case, I tried the Field Map in the https://pro.arcgis.com/en/pro-app/latest/tool-reference/conversion/export-table.htm Export Table tool, thinking this would be great. Well I got the field but it's all Null. Here's my window of how I set it up. I hit the plus button to add a field I called SiteID, then I hit Add New Source and found the SiteID field in the SQL view.

Here's my exported table all Null

[AndreaB_ · Accepted solution]

ah ha! I cracked the case and solved the mystery. I knew sometimes it would give me the ObjectID from SQL and sometimes it wouldn't and I finally figured out when/why. For @SSWoodward and others in the future - 

The difference is how I path to the SQL table in the tool. If I use the Map in any way, then the ObjectID from SQL gets removed. But if I go straight to the SQL database table it is preserved. Here are my tests:

• In Map, right click table – export table window. No modifications to field list: no PropertyID field in exported table.
• Opened Export Table pane from geoprocessing pane, pick input table from Map: no PropertyID field in exported table.
• Right click on geodatabase in Catalog pane – Import – Table - Table to Geodatabase. Pick table from map – no PropertyID field in exported table.
• Right click on sql table in Map – Data - export Table – no PropertyID field in exported table.
• Opened Export Table pane from geoprocessing pane – use folder icon to path to sql table: PropertyID exports.
• Right click on SQL table from within Catalog pane and Export – Table to Table - PropertyID exports.
• Right click on geodatabase in Catalog pane – Import – Table - Table to Geodatabase. – use folder icon to path to sql table: PropertyID exports.

I'm using ArcGIS Pro 3.3.5.

Hope this helps someone else in the future. And Esri - please document this somewhere! Tagging @KoryKramer for his opinion.

[SSWoodward · Accepted solution]

Okay thank you for the additional information, I'm fairly certain I understand what's happening and why.

All of your 'failure' cases are exporting from the layer in your map.  All of your 'success' cases are exporting from the feature class ( view ) in the database.  For all intents and purposes here, a view is a feature class. 

I am inferring that your view is created in the database with a SELECT statement where none of the selected fields are a database maintained unique identifier.  Let's say these are both integer fields; 'field_a' and 'field_b'.

In order to add this view to the map, ArcGIS Pro will create a query layer, and in that process will ask the user to define the unique identifier for the layer in the map.  This is only for the layer in the map, and does not apply to the view in the database. 

Lets say we tell ArcGIS Pro that 'field_a' is the unique ID for the layer. Once you have defined this unique ID, this is that feature layers 'OBJECTID' and the field will be managed by ArcGIS Pro accordingly. When you use this *feature layer* as input for an export operation, the software is going to manage that field just like it would an OBJECTID field. So on export, you lose it, and it's replaced with an OBJECTID field with new sequential values.  Exporting from our hypothetical layer would produce a table with two fields; 'OBJECTID' & 'field_b'.

When you export from the view directly, and not the layer in the map, that view lacks a unique identifier. Since it has no unique ID, exporting it to a geodatabase table will add an OBJECTID field, and maintain all of the original fields in the table.  So the resulting table will have three fields; 'OBJECTID', 'field_a', and 'field_b'.

Is this in line with the behavior that you are seeing?

[AndreaB_]

Yes, that is the behavior. Thank you. The only thing I would add is that ArcGIS Pro does not ask the user to define the unique identifier for the layer in the map in this scenario. Pro does it automatically and so the user doesn't really know what's happening. For example, when I add that SQL table ("view" but in Pro it just looks like a table in the SQL database) to my ArcGIS Pro map, it just adds it, no windows pop up or messages etc. You don't know that field is called an "ObjectID" until you right click on the table layer in the Map and go to Data Design -  Fields.

Confusing? I would say so.

[SSWoodward · Accepted solution]

Yes you're correct.  If your feature class or table lacks a defined ID field and contains a 32-bit, unique, not nullable integer, ArcGIS Pro will detect the qualifying field and set it as the unique identifier.  You can read more about that here. 

In my example, both of my integer fields were nullable, which means they would not be automatically selected as a unique ID field, prompting the pop-up.  If a field, for instance, your SiteID field, is present that meets the requirements outlined in the doc, that field will be marked as the unique ID field.