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

[ShaharLevenson]

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.

[ShaharLevenson]

EDIT:

Summary and a decent solution:

1. Working in a database that is not geo-enabled, you may have some difficulties keeping your primary-key fields in ArcGIS tools' results.

2. If your data doesn't have the ArcGIS maintained OBJECTID field, ArcGIS will look for another candidate.

3. If you have a not-null - unique - integer field (like many primary keys), ArcGIS will treat it as the OBJECTID and will define it's data type as "Object ID". You cannot tell ArcGIS to ignore that field.

4. The problem with this is that when you run a tool that creates a new Feature Class / Table from your data, ArcGIS populates the OBJECTID field with new values, making you lose the option to do joins based on that field.

5. Here are a few workarounds:

     a. Create a view from your data in your database and run the tool against the view. ArcGIS doesn't recognize the field in the view as OBJECTID.

     b. Don't choose integer for your primary key field. Use numeric, float, etc.

     c. Add your data to ArcGIS as a query table with a virtual key. Note that there may be a problem if you're using "Database Connection" - Try using "OLE DB Connection".

[EricMartinson]

Thank you, Shahar! Your observation that the "Add OLE DB Connection" function is now hidden in a button instead of being part of the add DB connection dialog helped me solve a nagging mystery!

--Eric