I have a SQL Server 2000 DB that has numerous tables that I would like to reset back to how they would have been just after creation( i.e. blank fields throughout) I'd like a number 1 to appear in the first index column when a new record is added. At the moment when I add a new record number 14 appears but i cannot access any of the records prior to that to delete them. How can I wipe out all of the existing records from thast table whilst still keeping the table intact ?
Many thanks for your help
This conversation is currently closed to new comments.
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 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.
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
SQL Indexes/table resetting
Many thanks for your help