0

Reinsert a deleted record back at the same Id while id is a Primary key. Sample code below.

Select * from tblTest where Id=1
delete from tblTest where Id=1
Insert INto tblTest
  values ('x','y') but in the place of Id= 1

The issue is:

  1. The table now start as Id= 2 when I do a (Select * from tblTest)
  2. I can't reseed Identity
  3. I don't want to restore the .db just for 1 record.
Dale K
  • 25,246
  • 15
  • 42
  • 71
  • 1
    [SET IDENTITY INSERT ON](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15) – Dale K Nov 17 '20 at 19:21
  • 1
    If you're worried about the value of the `IDENTITY` column, then `IDENTITY` is the wrong choice. It's by design that an `IDENTITY`'s value isn't reused if a row is deleted. A `IDENTITY`'s value of designed to be nothing more than an arbitrary, always ascending number; nothing more, nothing less. – Thom A Nov 17 '20 at 19:23
  • @DaleK That did the trick. :-) Thank you –  Nov 17 '20 at 19:53
  • @Larnu I totally agree with you. But my issue was I didn't want the table Id value to start 2 it was bothering me. Thanks for your tip. –  Nov 17 '20 at 19:57
  • @DaleK yes! totally did! :-) –  Nov 17 '20 at 19:57

0 Answers0