SQL Table Field Type OBJECT_ID

[EmilyLaMunyon]

Hello,

I have an ODBC connection in ArcCatalog to a non-spatial SQL table. I need to put the table in a SDE Geodatabase and am having some issues.

When viewing the non-spatial SQL table fields in ArcCatalog I noticed that one of the field types was changed from int to OBJECT_ID, I assume this is becasue it needs a unique key field. However, this is causing problems when I try and import the SQL table into a geodatabase, it drops the field and adds another OBJECT_ID field. I need this field to be there, it is important.

I have tried creating an empty table in SDE that includes the fields/schema I need and then appending the rows from the SQL table to the empty table and in the process mapping the fields to try and bring the one being dropped over. The problem is that it is not populating the field with the values I need.

Please help, I am at a loss!!

Thanks in advance! 🙂

[JakeSkinner]

Hi Emily,

You are correct about the unique key field.  When importing a table to a geodatabase, the unique key field will automatically be changed to an OBJECTID field.  Does the SQL table reside in the SDE geodatabase, or is it a separate database?  If it is in the same SDE geodatabase you can use command line to register the table with the geodatabase and specify which field you would like to be the row ID column (unique key field).  Ex:

C:\> sdetable -o register -t XY -c ID -C SDE 

In the above example, I am specify the row ID column to be ID.

Another option would be to drop the unique index on the SQL table field, recreate an index that is not unique, and then import the table into the geodatabase.  Your original field will remain with the same values, and a OBJECTID field will be created.  The OBJECTID field will be the new unique key field in this case.

[EmilyLaMunyon]

Thanks Jake for the reply!

The table is not in the same geodatabase, I am viewing it through an ODBC connection. I want to get it into the same geodatabase though and this is where the problems are occurring.

I would like to be able drop the unique index on the SQL table field, recreate an index that is not unique, and then import the table into the geodatabase. I have tried this with no luck, could you provide the steps to do this?

Thanks so much!!

[JakeSkinner · Accepted solution]

Here is a preview of my table in SQL Server in design view with the primary key applied to the ID field:

[ATTACH=CONFIG]11668[/ATTACH]

Right-click on the ID field > Indexes/Keys > choose to Delete this index:

[ATTACH=CONFIG]11669[/ATTACH]

Right-click on the ID field > Indexes/Keys > create a new Clustered index:

[ATTACH=CONFIG]11670[/ATTACH]

Save the changes you have made, then import the table into the SDE geodatabase.  Afterwards, the ID field will remain and a OBJECTID field will be created:

[ATTACH=CONFIG]11671[/ATTACH]

Note:  The ID field will now be able to contain duplicates.

[EmilyLaMunyon]

Thanks so much!!

[AjaySisodia2]

Hi Jake:

I am having the same issue. the screen shot links are broken, could you please fix that.

Also, is there a way to run this process in command line so I can batch import, I have many tables that I need to import into SDE.

Thanks so much.

Here is a preview of my table in SQL Server in design view with the primary key applied to the ID field:

[ATTACH=CONFIG]11668[/ATTACH]

Right-click on the ID field > Indexes/Keys > choose to Delete this index:

[ATTACH=CONFIG]11669[/ATTACH]

Right-click on the ID field > Indexes/Keys > create a new Clustered index:

[ATTACH=CONFIG]11670[/ATTACH]

Save the changes you have made, then import the table into the SDE geodatabase.  Afterwards, the ID field will remain and a OBJECTID field will be created:

[ATTACH=CONFIG]11671[/ATTACH]

Note:  The ID field will now be able to contain duplicates.