topic import table from sql server to file geodatabsae in Data Management Questions

import table from sql server to file geodatabsae

StephanieSnider — Tue, 10 Jun 2014 22:22:53 GMT

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?

Re: import table from sql server to file geodatabsae

VinceAngelo — Wed, 11 Jun 2014 01:58:27 GMT

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

Re: import table from sql server to file geodatabsae

StephanieSnider — Wed, 11 Jun 2014 14:19:00 GMT

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.

Re: import table from sql server to file geodatabsae

StephanieSnider — Wed, 11 Jun 2014 14:50:18 GMT

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!

Re: import table from sql server to file geodatabsae

VinceAngelo — Wed, 11 Jun 2014 15:04:29 GMT

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

Re: import table from sql server to file geodatabsae

StephanieSnider — Wed, 11 Jun 2014 15:42:10 GMT

Creating a view of the table will work for my needs. We don't have ArcSDE....no license for it. Thanks.