shahar04

Keeping the original ID field values in SQL Server by creating a query table

Discussion created by shahar04 on Jun 23, 2014
Latest reply on May 11, 2015 by ehmesri
Hello all,

We're migrating from an Enterprise Oracle database to SQL Server. Neither are GEOdatabases.
The tables within contain ID fields which are Integer primary keys. ArcGIS recognizes those keys as datatype Object ID, but only in SQL Server.

Whenever I run a GIS tool that generates a new table (import/export; batch geocoding; etc.) ArcGIS creates a new OBJECTID field in the result table while leaving out the original ID field. Needless to say, the original ID is crucial for later analysis, specially since the new OBJECTID field has no real meaning (just ascending numbers).

The solution was to create a query table, add to it a virtual key field, and run the different tools on the query table. I tried it but got the error "000055: Cannot create a Query Table for this workspace". I was surprised to learn that query tables cannot be created in a database connection, only in an OLE DB connection (or geodatabase - irrelevant to my case).

So, in order to run GIS tools while keeping my original table ID in SQL Server, I had to do the following:
1. Find the well-hidden "Add OLE DB Connection" button on ArcCatalog.
2. Create an OLE DB connection.
3. Make a new query table from my original table while adding a virtual key.
4. Run the tool.

There must be a better way to do this!

Some points for esri to consider:
1. Why only SQL Server recognizes the ID fields as Object ID datatype? Why not Oracle?
2. Can you make an option for not changing the object id field values? If not, can't you add a new OBJECTID field while keeping the original Object ID datatype field in the results?
3. Why can't I create a query table in a database connection? Why must I go to OLE DB connection?
4. Why does the "Add OLE DB Connection" button exists only in ArcCatalog? Why not in ArcMap as well?

Tech Specs:
Oracle 11g
SQL Server 2012
ArcGIS Desktop Standard 10.2.1

Best regards,
Shahar.

Outcomes