control_sql_-_resequence_the_gl_table [Support]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.

======  ======



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



<html><div style="margin-left: 40px;"> NewID : integer </div></html>
  - 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 =====



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



===== Version Information =====
  * Entered : 5/2012
  * Version : 4.5+