[GIS] Reset Object ID After Truncating Table In SQL Server

arcobjectsenterprise-geodatabasespatial-databasesql server

I am working on a program to export data from a view containing a geometry type in Sql Server 2008, to an ArcSDE table on another server which also stores data using the geometry type. The source database is not running ArcSDE. This will be a nightly job.

Following the documentation at the link below, I am able to generate an Object ID with every new insert:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#/in_SQL_Server/006z00000017000000/

My problem (if it is indeed a problem) is before I copy records between the two servers, I first delete all items in target table using a SQL statement: 

DELETE FROM target_table_name

However, when I insert the first record after the delete, the ObjectID starts off at the next highest value from before when I deleted the table. For example, lets assume that the highest ObjectID in the table before I deleted it was 1000. After I delete the contents of the table, and start to load new data, the new ObjectID the dbo.i_get_ids stored procedure returns is 1001. Each load has around 100,000 records and since this runs nightly, the ObjectID can become a pretty big value pretty quickly. My question is, is there a way I can reset the value? If I use the following ArcObjects code to truncate the table the ObjectID is reset to zero:

((ITable)targetTable).DeleteSearchedRows(null);

However, if there is a way to do this without ArcObjects, it would be preferred.

Best Answer

You can run the following command in SQL Management Studio to "reseed" the autonumber:

DBCC CHECKIDENT (target_table_name, RESEED, 1)

http://msdn.microsoft.com/en-us/library/ms176057.aspx

However this could well mess up ArcSDE (I think it is the ArcSDE database you are running it on). You may want to consider using a different key rather than an auto-generated one to reference your records (e.g. to select/update/display as an attribute), and leave the ObjectID alone. Having huge values in this column is not an issue in itself.

Related Question