How to "truncate" table to restart from lowest auto increment value

Question

How to "truncate" table to restart from lowest auto increment value

Navigation

#1 by (2 votes)

0

The tabela_A has a id field with auto increment. I need to clear the table with something similar to the command truncate only cleaning only the registry from 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 look something like:

TRUNCATE table WHERE id >=30

And return

id col1
1  A
3  B
4  C
5  D

sql sql-server

asked by anonymous 20.04.2016 / 23:25

1 answer

Wordpress Loop coming out unlike! get the value of input fields from a gridview with jquery

score 2 · Accepted Answer

First we remove the records using

DELETE FROM tabela WHERE id >= 30

Then we reset the autoincrement 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 different record from the original