Auto create ObjectID on tables imported into ArcGIS Pro from a database

[nicmah]

Hi,

I'm trying to work with tables in a relational database. I don't need to edit the data, I just need to link it to postcodes or regional data for display, or summarise the statistics for a dashboard. 

When I bring in the tables from the database, the first column is being used in ArcGIS Pro as the ObjectID. Is there any way for me to force it to create an ObjectID so I can still use that column, usually it's the primary Key. This seems to be preventing me from joining tables. 

Ideally I don't want to have to save the tables each time there is an update because the tables are updated frequently and saving then joining the tables each time would be a huge amount of work and make this process unachievable. 

[James_OKane]

Hi @nicmah,

Easiest way that I know of is using the Add Incrementing ID Field (Data Management) tool. The nice thing about that one is it works for tables you don't intend on importing into a geodatabase but want to use for joins or relates. It sounds like that's what you're after? It should add the auto object id field for you so new records automatically get a new OID. 

Hope that helps. 

Cheers,
James

[nicmah]

Thanks James. Database owner isn't keen for me to modify the database so I don't think this solution will work 

[James_OKane]

Ah I see. Well if you import the table into Arc Pro (say into a fgdb) as a table an objectid is automatically created for it. Is that an option?

[nicmah]

I tried exporting the tables but that didn't work. For whatever reason the resultant tables were empty. 

[DavidSolari]

Is the database table the foreign table? If so you may be able to wrap it in a registered view that creates a unique ID field for you. With SQL Server the query is something like:

SELECT
	CAST(ROW_NUMBER() OVER (ORDER BY allRecords.[SortField]) AS int) AS [OBJECTID],
	allRecords.*
FROM
	[database].[user].[table] allRecords

[nicmah]

Hi David. The database consists of about 30 tables, and I want to join them, and join it to a postcode layer. 

My programming skills are terrible, where would I apply that query? (Or possibly get the DBA to apply it as I'm just setting up a link to access the tables and am not the DBA).

[DavidSolari]

If you have a DBA they would create the view and register it with the EGDB so you can work with it in Pro. They'd presumably have the knowledge to create a table view with an extra identity field for whatever your DB provider is. This requires creating a new view in the DB but it won't interfere with important processes so they hopefully won't have any issues with your request.

If both tables in your join are in the same DB, another thing you can try is creating a Query Layer. That way you can join your tables together without worrying about what Pro uses as the ObjectID.