truncate particular id from table

Guest User

4y876SQL Server

truncate ->this resets the entire table, is there a way via truncate to reset particular id

for example: i want to delete id from 96 to 99,so next time when new entry generated its starts from id 95

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted answer
@Nirvriti,
if you only want to insert a row at the end, and you are not bothered about the middle ones then you need to use this
declare @max int
select @max=max([Id]) from YourTable
if @max IS NULL
  SET @max = 0
DBCC CHECKIDENT ('YourTable', RESEED, @max)
@Nitin sir, yes correct you are as per her question, DBCC is the way to go. But based on her replies a proposed a different solution.
Guest User4y ago
if i delete from id 96 to 99 and next time when data is insert it starts from 96 not 95
Nitin Sontakke4y ago
@Sachin, we sincerely believe that we correctly addressed the question.

@Nirvriti, in your question posted above, if you delete records from 96-99, you will expect to insert record with Id with 96 and not 95. Please clarify! Also you are expecting to insert records ONLY towards end and NOT with Ids in between as being suspected by Sachin.
Sachin Singh4y ago
There is no command for that.
Also, there is a huge purpose that any DBMS doesn't allow that to do, we call it database integrity.
Even after using DBCC, your table won't be rearranged magically, only the next inserted rows will start from the new seed, not the middle deleted ones.
To rearrange the whole table you will have to do it manually
Delete from YourTable where PrimaryKeyColumnName=5;
SELECT * into #temp from YourTable ;
TRUNCATE TABLE YourTable ;
SET IDENTITY_INSERT YourTable ON;
INSERT INTO YourTable (PrimaryKeyColumnName,Col2,col3,other_columns)
SELECT ROW_NUMBER() over(order by PrimaryKeyColumnName),Col2,col3,other_columns FROM #temp
SET IDENTITY_INSERT YourTable OFF;
Jaydeep Patil4y ago
Hi,

Check this and try to resetting the identity

https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server
Nitin Sontakke4y ago
Just stating that 'it doesn't work' is not quite sufficient.

You may please paste some screen shots. Paste the commands you issued and the output you got.

Furthermore, you may please carry all of this on a COMPLETELY separate table, just for the purpose of TESTING.

Hope it clarifies!
Guest User4y ago
i already used DBCC CHECKIDENT but doesnt work
Nitin Sontakke4y ago
You may wish to use DBCC CHECKIDENT command.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-ver16

Please refer to the above documentation. There is a way to RESET identity value of your choice.

Hope it helps!
Rajanikant Hawaldar4y ago
Check by resetting the identity