Last post Jan 10, 2012 04:39 AM by pchindhade
Jan 09, 2012 06:52 AM|pchindhade|LINK
How i can reset the table's identity column after deleting any one record from table.
Now if i delete anything from table suppose if i delete 2 number of record than table is looking like 1,3,4,5,...
and i want to make it 1,2,3,...when ever i delete any record...
Jan 09, 2012 06:54 AM|Guojiawei|LINK
DBCC CHECKIDENT ('tablename', RESEED, 1);
Jan 09, 2012 06:56 AM|Guojiawei|LINK
Jan 09, 2012 06:56 AM|kedarrkulkarni|LINK
after delete command either do this
DELETE FROM TABLE_NAME
DBCC CHECKIDENT (TABLE_NAME,RESEED, 0)
or instead of delete command to remove data... use truncate
TRUNCATE TABLE TABLE_NAME
this sould reset ideneity too
hope this helps...
Jan 09, 2012 06:59 AM|om_prakashp|LINK
To see the current value, use the following SQL command:
To Reset, use the following SQL command:
DBCC CHECKIDENT('table_name', RESEED, [new_reseed_value])
DBCC CHECKIDENT('my_table', RESEED, 10)
Jan 09, 2012 07:02 AM|Sankalpa|LINK
Use Truncate statement instead of Delete
more found there
Jan 09, 2012 07:47 AM|pchindhade|LINK
it is showing the error like...DBCC statement is not supported what should i do...??
Jan 09, 2012 10:08 AM|shivv|LINK
Use this link to fix the issue.
Jan 10, 2012 01:01 AM|Sankalpa|LINK
Hi i think you cant just delete and wait for result here.
Delete the desired Record.
Then copy the remaining record into temp table.
Truncate the table.
Insert back to original table from temp table.
CHECK THE EXSAMPLE
--if your table name is Table2 and it has columns Id (idnetity value), Value
--delete the desired record
Delete from dbo.Table2 where id = 3
--insert the values into #tmp table
select VALUE INTO #tmp from dbo.Table2
--just check is value there
SELECT * FROM #tmp
--truncate the original table
TRUNCATE TABLE dbo.Table2
--insert back into original table
INSERT INTO dbo.Table2
SELECT VALUE FROM #tmp
--remove the #tmp table
DROP TABLE #tmp
If you still not found the answer better to post your problem @ sqlforum
Jan 10, 2012 04:18 AM|pchindhade|LINK
guys i m still not getting the anwser ...plase how to reset records after deleting single record from table. it is still showing the same ...i have tried all the example above...
Jan 10, 2012 04:34 AM|Sankalpa|LINK
If you tried my code it should work , you can't update the idetity inserted value but you can insert a misssin value with idnetity insert on statement
Jan 10, 2012 04:39 AM|pchindhade|LINK
ya got it thanks for new solution...thank you very much...