truncate particular id from table

Question

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 99so next time when new entry generated its starts from id 95

Sachin Singh · 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.

Nitin Sontakke · Answer

@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 Singh · Answer

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 (PrimaryKeyColumnNameCol2col3other_columns) SELECT ROW_NUMBER() over(order by PrimaryKeyColumnName)Col2col3other_columns FROM #temp SET IDENTITY_INSERT YourTable OFF;

Guest User · Answer

if i delete from id 96 to 99 and next time when data is insert it starts from 96 not 95

Jaydeep Patil · Answer

Hi Check this and try to resetting the identity https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server

Nitin Sontakke · Answer

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 User · Answer

i already used DBCC CHECKIDENT but doesnt work

Nitin Sontakke · Answer

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 Hawaldar · Answer

Check by resetting the identity