Often while testing our application, we perform several insert/update/deletes on a table. Assuming the primary key (identifier) was an AUTO increment ID field, after several test runs there is a point – where we would like to start fresh – again!. (A familiar pattern while developing/debugging)
After deleting the table records, my next ID inserted in the table starts from the last inserted value (because this set as auto increment). Say the last ID inserted in the table was 500, and after deleting all records, a new insert would start the ID value to be 501. In order to reset your auto increment identifier (to start from 1) uses the following:
DELETE FROM myTable -- (Optional – used to clear all records of myTable) DBCC CHECKIDENT('myTable', RESEED, 0) ;
The above command would reset the auto increment identifier to start from 1. Another option would be insert your custom identifier using the insert statement
To do as such, use the following:
SET IDENTITY_INSERT myTable ON ALTER TABLE myTable AUTO_INCREMENT =1 INSERT INTO myTable(autoId,Description,xmlInfo) VALUES(7,'This is a custom Info', N '..Some XML here..' SET IDENTITY_INSERT myTable OFF
The above script would insert ID 7 against the identifier column in the table. Note: The above script is also an example on inserting XML text (SQL 2005) in your xml data type.
Hope it helps.
Happy scripting 🙂