How to "truncate" table to restart from the smallest auto increment value

Asked

Viewed 70 times

0

To tabela_A has a field id auto increment. I need to clear the table with something similar to the command truncate only that cleaning only the record from the id = x.

For example: 

id col1
1  A
3  B
4  C
5  D
30 E
32 F
33 G
34 H

The expected result would be something like: 

TRUNCATE table WHERE id >=30

And return

id col1
1  A
3  B
4  C
5  D
sql sql-server

  • There’s something to stop you from doing it: DELETE FROM table WHERE id >= 30?

  • Doing this, the column id continue auto incrementing with values >= 30

1 answer

2


First we remove the records using

DELETE FROM tabela WHERE id >= 30

Then we reset the auto increment counter using

declare @ultimoID int
SELECT @ultimoID = Coalesce(MAX(Id), 0) FROM tabela
DBCC CHECKIDENT (tabela, RESEED, @ultimoID)

But it is not a good idea to reuse an ID number to identify a record other than the original

  • Thanks for the warning, but it was just a few tests.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.