Identity field reset after deleting test data 1

[T3leTech]

Products being used: Access 2000, .adp file for front end forms and reports; SQL Server 2000 for all the "backend" stuff, like tables stored procedures, etc.

I have completed building and testing a database that will be used in production Oct 1st (next Friday). I need to delete all of the testing and training data that currently exists in the two main tables, both of which have auto-number identity fields. The deleting of the data is not the problem...how to reset the identity fields to zero is.

Does anyone know if this is possible? I don't want to change the structure of the tables (i.e. delete the two auto-number fields, and then recreate them) unless absolutely necessary. What I'm looking for is some "magic" command that "resets" the auto-number to zero.

Thanks for any suggestions.

--Telephony Tech

 

[donutman]

The way that I do it is to remove the identity definition from the column after deleting the existing data and then re-adding the identity property.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[T3leTech]

And that resets the auto-number to zero?

So...you delete all of the data, then change the identity definition (to false) and then change it back (to true)? Is that correct?

--Telephony Tech

 

[donutman]

Yep

 

[pkailas]

if you just want to delete the data in the table and reset the identity to 0 it's very very easy.

Truncate table [tablename]

that will do what you want.

 

[T3leTech]

What if there are constraints on the table? Do they have to be removed and then reapplied after the data is truncated?

--Telephony Tech

 

[pkailas]

well let me ask this. Are you able to use the command "Delete from [table name]" now?"

how were you planning on removing the data from that table? Are there data in that table that you do NOT want deleted?

 

[T3leTech]

I haven't tried either command yet (delete or truncate). No, there is no data that needs to be saved, but the constraints need to be kept. Can I just change the referential integrity so that the delete will cascade to the FK?

--Telephony Tech

 

[pkailas]

Well if you want the corresponding data in any linked tables to be destroyed with the data in that table, I would set up the references appropriately in any case. If the truncate is unsuccessful, then try deleting the data in the linked tables first then truncate the main table.

 

[tlbroadbent]

You can delete the data in the table and then run the following statement to reset the identity value to 1.

DBCC CHECKIDENT (table_name, RESEED, 1)


If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm

 

[T3leTech]

Wow!!! Thank you Terry! That is exactly what I was looking for. Thanks also to "donutman" and "pkailas" for helping me realize I needed to change the relationship to "on delete cascade".

Thanks again!!!

--Telephony Tech