Explanation of SQL
This Query is use resequence the IDs in the GL table. It should only be necessary if the GL table has some corruption and ends up with duplicate IDs.
Risk of Data Corruption if Run Improperly
High. Data is modified in this query. Do not run this except under the direction of a Cyrious Technical Support staff member. Doing otherwise may result in lost or contaminated data. All data modifications done through direct SQL are permanent and non-reversable.
Steps
- Create a new field on the GL called
NewID : integer
- Run the SQL below
- Delete the new field called NewID
Notes: For faster processing, you can
- Drop the clustered primary index and recreate it afterwards
- Add an index on NewID
SQL
-- Update Ledger Set NewID = 1 where NEWID <> 1 DECLARE @counter INT SET @counter = 1000 SET NoCount ON WHILE @counter<1000000 BEGIN UPDATE TOP (1) L SET NewID = @counter FROM Ledger L WHERE NEWID = 1 ; SET @counter = @counter + 1 END SET NoCount Off ; UPDATE Ledger SET ID = NewID WHERE ID > 0 ; SELECT top 100 * FROM Ledger
Version Information
- Entered : 5/2012
- Version : 4.5+
You could leave a comment if you were logged in.