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.
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
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 1,129
Downloads 394
CodeID
DB ID
