If you want to quickly remove all rows from a table and begin the table from scratch TRUNCATE is the best approach. It is quicker too since it logs nothing (so beware).
TRUNCATE is only for destroying all data in a table though. What if you want to remove only the last few rows and then begin the seeding off from the last primary key?
Use CHECKIDENT to reseed
You can delete the rows you want and after you can reset the SEED of the table to what you desire using the following line:DBCC CHECKIDENT('myTable', RESEED, 10)Note: the 10 is a seed so you would set this to be the current maximum identity. So if you reseed with 10 the next insert will yield 11 as the primary key.
Posts
Hope this helps someone:
ReplyDelete-- Get the seed in case of rollback.
SELECT @tbl_Seed = (MAX(ID)) FROM [tbl]
-- Do stuff...
-- Upon rollback
IF NOT @tbl_Seed IS NULL BEGIN
PRINT ''
PRINT 'Reseeding [tbl] to original identity seed...'
DBCC CHECKIDENT ('[tbl]', RESEED, @tbl_Seed);
END
Excellent read, Positive site, where did u come up with the information on this posting? I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work,If anyone interested similar one's have a look here thanks.
ReplyDeleteshih tzu puppies for sale near me
buy puppy online
fuhrerschein-kaufen-schweiz