Export data out of SDE in consistent order each time

359
4
04-27-2012 03:14 PM
DanSereno
New Contributor
Hello All,

My problem is that I need a unique, maintained attribute field to perform SQL on later in my workflow in order to maintain database linkages in a computer aided dispatching system between rebuilds.

Is there a way to have the records, being exported from an SDE using Feature Class to Feature Class, populate the newly created feature class in the same order each time?
In other words, when exporting data, from the SDE, I would like FID 1 to be the same record each time I export the same feature class. Currently, each time I export data FID 1 ends up being a different record.

Thanks,
Dan Sereno
GIS DBA
0 Kudos
4 Replies
VinceAngelo
Esri Esteemed Contributor
The only way to be certain of a consistent order is to apply an ORDER BY on the query.

It's probably not particularly safe to rely on the objectid for this purpose.

- V
0 Kudos
DanSereno
New Contributor
Thank you for the reply Vangelo.
However, unless I am doing something wrong, placing "order by OBJECTID" into the "Expression" portion of the Feature Class to Feature Class tool produces the message: Invalid SQL syntx[Microsoft SQL Native Client: Deferred prepare could not be completed.]

Thank you,
Dan Sereno
GIS DBA
0 Kudos
VinceAngelo
Esri Esteemed Contributor
The order_by clause is a separate component in the ArcSDE SE_QUERY_INFO object used
to query the stream. I haven't looked to see if it's available from ArcObjects, but it's unlikely
to be available though the where clause in that tool.  You might be able to use an ORDER BY
in a subquery, but there's still no guarantee that a query (especially a spatial query) would
honor the subquery order.  It is very likely that the resulting query would be much slower.

Even if the export order is reliable, there's still no guarantee that the objectid insert order
will  always be increasing (generally, it is, but it's not requred), so you could still have subtle
errors in alignment.  I suggest you use a column other than the registered rowid column for
this purpose.  You'll most probably need to code your own export tool to achieve this goal.

- V
0 Kudos
DanSereno
New Contributor
Vangelo,
I have no write permissions to the SDE from which I am looking to extract data.
So, unless there is some type of "order", on which any of the many tools that can extract a data set from an SDE operate, it seems I have no way of even writing my own tool to extract records in the same order each time from an SDE.

Thanks,
Dan Sereno
GIS DBA
0 Kudos