Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision
Previous revision
compress_macro_ids_in_a_dataset_using_sql [2019/01/30 09:22]
127.0.0.1 external edit
compress_macro_ids_in_a_dataset_using_sql [2019/02/06 10:40]
kcifreo
Line 1: Line 1:
-======  ====== +======   ======
- +
  
 This document describes the process (and SQLs) used to compress the IDs in a table (or many tables). For example, if you started with a table with 100 records that were scattered between ID=1001 and ID=5873, this routine could compress them so that the IDs ranged from 100 to 200. This document describes the process (and SQLs) used to compress the IDs in a table (or many tables). For example, if you started with a table with 100 records that were scattered between ID=1001 and ID=5873, this routine could compress them so that the IDs ranged from 100 to 200.
  
- +**WARNING: This article is for reference. The changes made will PERMANENTLY affect your data. This should only be carried out by a skilled implementer and SQL practitioner. Always Backup your data before running this routine.**
- +
-<html><div style="margin-left: 40px;"> WARNING: This article is for reference. The changes made will PERMANENTLY affect your data. This should only be carried out by a skilled implementer and SQL practitioner. **Always Backup** your data before running this routine. </div></html> +
- +
- +
 ===== Concept ===== ===== Concept =====
  
 +The idea of this approach is to use SQL queries to renumber and insert the relevant data from one table to another. The basic approach for each table will be:
  
- 
-The idea of this approach is to use SQL queries to renumber and insert the relevant data from one table to another. The basic approach for each table will be: 
   - Create a map table (containing TABLENAME, OLDID, NEWID) for Tables to be compressed   - Create a map table (containing TABLENAME, OLDID, NEWID) for Tables to be compressed
   - For each table to Compress:   - For each table to Compress:
-    * Load the map table +      * Load the map table 
-    * Compute the NewIDs+      * Compute the NewIDs
   - ReMap each field in the database   - ReMap each field in the database
- 
- 
  
 ===== SQL ===== ===== SQL =====
- 
- 
  
 <code> <code>
Line 87: Line 75:
             SET @S = @S +             SET @S = @S +
                         'FROM '+@TableName+' '+                         'FROM '+@TableName+' '+
-                        'WHERE ID > 0 '+                        'WHERE ID> 0 '
             Execute(@S);             Execute(@S);
             -- Update the Map with the ID of the Destination Table for the remaining NewIDs             -- Update the Map with the ID of the Destination Table for the remaining NewIDs
Line 95: Line 83:
             FROM IDMap as TheMap             FROM IDMap as TheMap
             LEFT JOIN             LEFT JOIN
-              (Select TableName, OldID, ( SELECT COUNT(1) FROM IDMap I2 WHERE I2.TableName = I1.TableName AND I2.OldID < I1.OldID ) as NewID+              (Select TableName, OldID, ( SELECT COUNT(1) FROM IDMap I2 WHERE I2.TableName = I1.TableName AND I2.OldID <I1.OldID ) as NewID
                FROM IDMap as I1 WHERE (TableName = @TableName) ) MapSum on MapSum.TableName = TheMap.TableName and MapSum.OldID = TheMap.OldID                FROM IDMap as I1 WHERE (TableName = @TableName) ) MapSum on MapSum.TableName = TheMap.TableName and MapSum.OldID = TheMap.OldID
             WHERE (TheMap.NewID IS NULL) and (TheMap.TableName = @TableName)             WHERE (TheMap.NewID IS NULL) and (TheMap.TableName = @TableName)
Line 146: Line 134:
 exec Fill_IDMap 'RuleMacro' exec Fill_IDMap 'RuleMacro'
 exec Fill_IDMap 'RuleAction' exec Fill_IDMap 'RuleAction'
-exec Fill_IDMap 'Journal'       , 'ID in (Select ID from RuleAction where ID > 0 union Select ChildItemID as ID from RuleAction where ID > 0  AND ChildItemID IS NOT NULL)'+exec Fill_IDMap 'Journal'       , 'ID in (Select ID from RuleAction where ID> 0 union Select ChildItemID as ID from RuleAction where ID> 0  AND ChildItemID IS NOT NULL)'
 exec Fill_IDMap 'ReportMenuItem', 'ID in (Select ChildItemID from RuleAction where ChildItemClassTypeID IN (17240,17250,17270,17290))' exec Fill_IDMap 'ReportMenuItem', 'ID in (Select ChildItemID from RuleAction where ChildItemClassTypeID IN (17240,17250,17270,17290))'
 exec Fill_IDMap 'EmailActivity' exec Fill_IDMap 'EmailActivity'
Line 174: Line 162:
 -------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------
 </code> </code>
- 
-