Reset ID Column

How to reset ID Column in Microsoft SQL, (not completed yet).
This is because MSSQL starts inserting ID after 77, 78 and 79 as 3077, 3078 and 3079.
They said reason is preventing duplication.
Code below only shows SQL commands and sequence of executing, it does not actually executing them.


Public

Not Tested

My Own Work
UseTempTable=1 ' To use the table method instead of same table

RunSQL="select IDENT_Current('Projects') NextID,(Select Max(ID) from Projects) LastID"
' ….. Run SQL and read two columns
If NextID>LastID then

 ' Move added records between LastID and NextID into new rows and modify IDs
 MoveSQL="Insert into Projects(Pid, Name, Body, Type_1, Image, URL1, URL2, Order_1, Dated, DateAdded, Clicks, Views) " & vbcrlf & _
  "SELECT Pid, Name, Body, Type_1, Image, URL1, URL2, Order_1, Dated, DateAdded, Clicks, Views FROM dbo.Projects Where ID in (3077, 3078, 3079,3080,3081,3082,3084,3085,3086,3087,3088,3090,4090)"
 If UseTempTable=1 then
  ' Move these rows into new temporary table
  MoveSQL="Select Pid,Name,Body,Type_1,Image,URL1,URL2,Order_1,Dated,DateAdded,Clicks,Views into TempTable20160707 From dbo.Projects where ID between (" & NextID & ", " & LastID & ")"
  ' Don't forget to remove that temp table after done
 End If
 ' … Run sql cmd …

 ' Then we remove these additional Lines
 DelSQL="delete from projects where ID in (3077, 3078, 3079,3080,3081,3082,3084,3085,3086,3087,3088,3090,4090)"
 ' … Run sql cmd …

 ' Fix it using sql command below
 RunFixSQL="DBCC CheckIdent (Projects, Reseed, 76)"
 ' … Run SQL cmd …

 If UseTempTable=1 then
  MoveBack="Select Pid,Name,Body,Type_1,Image,URL1,URL2,Order_1,Dated,DateAdded,Clicks,Views into dbo.Projects From TempTable20160707"
  KillTemp="Delete TempTable20160707"
 End if
End If

Views 65 Downloads 18

SQL Database
ANmarAmdeen
695
Attachments
Revisions

v1.0