Web Development

You have wiped the extant records out, but the autonumber stays set to the next. In Access, and probably in SQL Server, you can open the table in design view, save it to a new name, delete the old table and rename the new table: that will give you numbering from 1 onwards.

In SQL Server I believe you can reset the identity seed on an identity column in an empty table.

In SQL Server you can use command TRUNCATE TABLE table_name.

In SQL BOL it says:
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column.

You need to use the truncate command to delete the data and reset the identity column back to its initial seed value. The delete command only deletes the data, but does not reset the identity column. The syntax of the truncate command is as follws:

TRUNCATE TableName

where TableName is the name of your table.

To apply this to several tables execute several truncate statements, one for each table you wish to truncate. If you want to truncate all the tables in the database, you may want to write a stored procedure with a truncate statement that cycles through the table names stored in the sysobjects table. Hope this helps.

This question was closed by the author