Differences
This shows you the differences between two versions of the page.
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.** | |
- | + | ||
- | < | + | |
- | + | ||
- | + | ||
===== 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: | ||
- | | + | |
- | * Compute the NewIDs | + | * Compute the NewIDs |
- ReMap each field in the database | - ReMap each field in the database | ||
- | |||
- | |||
===== SQL ===== | ===== SQL ===== | ||
- | |||
- | |||
< | < | ||
Line 87: | Line 75: | ||
SET @S = @S + | SET @S = @S + | ||
'FROM ' | 'FROM ' | ||
- | '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 < |
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 ' | exec Fill_IDMap ' | ||
exec Fill_IDMap ' | exec Fill_IDMap ' | ||
- | exec Fill_IDMap ' | + | exec Fill_IDMap ' |
exec Fill_IDMap ' | exec Fill_IDMap ' | ||
exec Fill_IDMap ' | exec Fill_IDMap ' | ||
Line 174: | Line 162: | ||
-------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------- | ||
</ | </ | ||
- | |||
- | |||