resetting the identity seed?

resetting the identity seed?

Post by Adri » Thu, 08 Jan 2004 00:27:25


I am loading my MS SQL tables. I have Identity=Yes and
Identity Seed =1 on the key field to the table I am
loading. I delete the table each time I run my load
program. My problem is the table is remembering the last
value that was in the identity field. I want the records
I insert each time I load to start at 1. This is not
happening. The first time I run, the first record has 1
for the key and 4221 records are inserted. When I rerun,
I execute a delete on the table, and start inserting
records. This time the key to the first record has the
value 4222 and the last record has the value of 8443.

How do I dynamically tell the table to reset the Identity
Seed? I have searched the online help, and have not
found the answer to this question.

All assistance is greatly appreciated.

Best,
Adria
 
 
 

resetting the identity seed?

Post by Eric Sabin » Thu, 08 Jan 2004 00:31:58

Instead of deleting the data from the table, run TRUNCATE on the table.
This will reset the identity.

hth
Eric

 
 
 

resetting the identity seed?

Post by William Mo » Thu, 08 Jan 2004 00:34:56

Assuming you're using "DELETE * FROM tblMyTable", there is a better option.
Try "TRUNCATE TABLE tblMyTable".
 
 
 

resetting the identity seed?

Post by Lasse Edsv » Thu, 08 Jan 2004 00:35:19

Adria,

look at TRUNCATE TABLE

in books online

it deletes all in the table and should resent identity seed i think....

/Lasse
 
 
 

resetting the identity seed?

Post by Keith Krat » Thu, 08 Jan 2004 00:40:17

In addition to the other posts, there is another method that you could use:
DBCC CHECKIDENT
check out the reseed argument.within Books Online (within the SQL Server program group).

--
Keith