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.

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.

1. Create a new field on the GL called

1. Run the SQL below
2. 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

-- 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

• Entered : 5/2012
• Version : 4.5+