import table from sql server to file geodatabsae

3371
5
06-10-2014 03:22 PM
Highlighted
Occasional Contributor III
ArcGIS 10.2.2.

I'm trying to import a table from a SQL Server database (not a geodatabase) to a file geodatabase.  In SQL Server, the table has a primary key that ArcGIS recognizes as an Object ID field.  When I import the table into the file geodatabase, ArcGIS ignores the primary key and creates a new Object ID field.  I need the primary key to stay with the table.  How can I import a table from a non geodatabase into a geodatabase and still keep the primary key?
5 Replies
Highlighted
Esri Esteemed Contributor
What is the name of the primary key column?  ArcGIS tends to assume that columns
named "objectid" are available for reassignment.

You don't say how you're going about the transition, or what language/interface you're
using, but one way to preserve your data is to recreate the table schema with an initial
objectid column and renaming the primary key into something obvious ("origid"), then
copying the contents, through a view or other query to include a row number in the
objectid spot, and renaming the original PK to the new name.

- V
Reply
0 Kudos
Highlighted
Occasional Contributor III
The primary key field name is "Interest_ID".  I can't modify the fields of that table because of a .NET application that uses it.  This application is the life blood of our organization, and we don't have a test environment.   For now away, modifying the fields even to add a new field is not a risk I'm willing to take as it would break our contract with the company that developed the app.  I've tried exporting the table from SQL Server to the file geodatabase using the geoprocessing tools but that removes the primary key.  I am able to do a join within the mxd and keep the primary key field, but I'd prefer a more permanent solution.  Ultimately, I'd like to set up this process in python to run each night so that the dataset is available to our staff....not just available through the map service.
Reply
0 Kudos
Highlighted
Occasional Contributor III
I found a solution.  I can create a view of the table in SQL Server.  Then I  export the view to the file geodatabase and it keeps the primary key field while adding an Object ID field.  Yeah!
Reply
0 Kudos
Highlighted
Esri Esteemed Contributor
File geodatabase isn't a full SQL implementation, and it doesn't honor a true primary key.

It's a "feature" of file geodatabase that an objectid column will be promoted from the first
integer column it finds (or a "Miranda" objectid column will be created), and the contents
assigned sequentially from 1, so it is not possible to copy this table into a file geodatabase
without either losing the Interest_ID value or changing table organization to include an initial
ObjectID column. 

The ObjectID is not really a true column, so your legal staff might be able to argue that you
haven't "changed" the table (though I do wonder about their willingness to sign off on such a
data use constraint).

- V
Highlighted
Occasional Contributor III
Creating a view of the table will work for my needs.  We don't have ArcSDE....no license for it.  Thanks.
Reply
0 Kudos