Reset Object ID After Truncating Table In SQL Server

Question

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.

Answer 1

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.

• SagebrushGIS: The data being loaded into the new table already has a primary key field. The issue with the ObjectID is just to make ArcSDE happy. I do not use the ObjectID for anything, other than because ArcSDE wants it loaded.
• geographika: The SQL code above should work then. You should be able to execute it through .NET if needed.
• SagebrushGIS: The ObjectID does not have an Identity seed on the physical table I am loading data in. The Object IDs are loaded from the "i" tables, which have one record showing the latest ObjectID issued, and some other information that I am not clear on. So it appears that there is no identity seed to reset.