Utility proc for updating (sub) sequence columns

,

This is a utility proc that I use a lot for datawarehouse transformation/load processing.

This is a generic proc for resequencing an integer column in sorted order within a given key combination.

Note that 'key' is used here in a general context and not specific, that is there doesn't have to be any keys or indexes on the columns. However, The lowest level key in the column combination must be unique at that level.

Up to four part keys can be specified (or modify this script to allow more).

SEE sample/test script in bottom comment of create proc.

CREATE PROC reseq_column
( @table_name    VARCHAR(30),  
  @seq_column    VARCHAR(30),  
  @key1          VARCHAR(30),  
  @key2          VARCHAR(30)= NULL,  
  @key3          VARCHAR(30)= NULL,  
  @key4          VARCHAR(30)= NULL,  
  @extra_key     VARCHAR(30)= NULL, --extra key to make row unique  
  @debug         INT = NULL  
) AS  
------------------------------------------------------------------------
-- Utility to resequence an integer column within a multipart key
--
-- This is a generic proc for resequencing an integer column in sorted 
-- order within a given key combination.

-- Note that 'key' is used here in a general context and not specific, 
-- that is there doesn't have to be any keys or indexes on the columns.  
-- However, The lowest level key in the column combination must be 
-- unique at that level.
------------------------------------------------------------------------
SET NOCOUNT ON  
DECLARE @SQL VARCHAR(6000)  
  
--sequence values into temptable  
-- use all keys make 1=1 to main table  
--last key provided is the sorting one (usually a measure for routes)  
SELECT @SQL = 'SELECT IDENTITY( INT, 1, 1 ) AS seq, '  
             +'       * '  
             +'  INTO #tempnewseq '  
             +'  FROM ( SELECT TOP 100 PERCENT '  
             +' ' + @key1   
  
IF ISNULL( @key2, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key2  
IF ISNULL( @key3, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key3  
IF ISNULL( @key4, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key4  
IF ISNULL( @extra_key, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @extra_key  
  
  
SELECT @SQL = @SQL +',0 as newseq'  
                  +' FROM ' + @table_name  
                  +' ORDER BY ' + @key1  
IF ISNULL( @key2, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key2  
IF ISNULL( @key3, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key3  
IF ISNULL( @key4, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key4  
IF ISNULL( @extra_key, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @extra_key  
                    
SELECT @SQL = @SQL + '  ) AS w '  
     
  
--now get min for each break  
--now group at 1 higher level (1 less key  
SELECT @SQL = @SQL + CHAR(13)+CHAR(10)  
  
SELECT @SQL = @SQL + ' SELECT ' + @key1   
IF ISNULL( @key3, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key2  
IF ISNULL( @key4, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key3  
     
SELECT @SQL = @SQL +  ' ,MIN( seq ) AS minseq '  
                   +'  INTO #tempSegSeq2'  
                   +'  FROM #tempNewSeq'  
                   +'  GROUP BY '+ @key1   
IF ISNULL( @key3, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key2  
IF ISNULL( @key4, '' ) <> ''   
   SELECT @SQL = @SQL + ' ,' + @key3  
  
--extrakey not used for break points, only for making unique    
     
-- now update the new subsequence  
-- (segseq2 is at higher level)  
SELECT @SQL = @SQL + CHAR(13)+CHAR(10)  
SELECT @SQL = @SQL + 'UPDATE #tempNewSeq '  
                   +' SET newseq = (seq - minseq ) + 1 '  
                   +' FROM #tempNewSeq  a '  
                   +' JOIN #tempSegSeq2 b ON a.' + @key1 + ' = b.' + @key1  
IF ISNULL( @key3, '' ) <> ''   
   SELECT @SQL = @SQL + ' AND a.' + @key2 + ' = b.' + @key2  
IF ISNULL( @key4, '' ) <> ''   
   SELECT @SQL = @SQL + ' AND a.' + @key3 + ' = b.' + @key3  
  
-- now go back and update main table  
SELECT @SQL = @SQL + CHAR(13)+CHAR(10)  
SELECT @SQL = @SQL + 'UPDATE ' + @table_name   
                   +' SET ' + @seq_column + ' = n.newseq '  
                   +' FROM ' + @table_name + ' s '  
                   +' JOIN #tempNewSeq n ON n.' + @key1 + ' = s.' + @key1  
IF ISNULL( @key2, '' ) <> ''   
   SELECT @SQL = @SQL + ' AND n.' + @key2 + ' = s.' + @key2  
IF ISNULL( @key3, '' ) <> ''   
   SELECT @SQL = @SQL + ' AND n.' + @key3 + ' = s.' + @key3  
IF ISNULL( @key4, '' ) <> ''   
   SELECT @SQL = @SQL + ' AND n.' + @key4 + ' = s.' + @key4  
IF ISNULL( @extra_key, '' ) <> ''   
   SELECT @SQL = @SQL + ' AND n.' + @extra_key + ' = s.' + @extra_key  
                     
  
IF ISNULL( @debug, 0 )  > 0 PRINT @sql  
IF ISNULL( @debug, 0 )  > 10 BEGIN  
   SELECT @SQL = @SQL + " SELECT * FROM #tempNewSeq ORDER BY seq "  
END  
  
IF ISNULL( @debug, 0 )  < 20 BEGIN  
   EXEC ( @SQL )  
END  
  
RETURN 0  
GO

/*************TEST/EXAMPLE*******************************************
For example: numbering cities within a state/county:

 CREATE TABLE #cities 
  (  state_name   VARCHAR(20)
    ,county_name  VARCHAR(20)
    ,city_name    VARCHAR(20)   
    ,seq_num      INT
  )  
INSERT INTO #cities SELECT 'NEW YORK',  'OSWEGO',   'PHOENIX', 0
INSERT INTO #cities SELECT 'ARIZONA',  'MARICOPA',   'PHOENIX', 0
INSERT INTO #cities SELECT  'ARIZONA', 'MARICOPA','TEMPE',        0
INSERT INTO #cities SELECT  'ARIZONA','MARICOPA','MESA',         0
INSERT INTO #cities SELECT  'ARIZONA','PIMA',    'TUCSON',       0
INSERT INTO #cities SELECT  'ARIZONA','PIMA',    'GREEN VALLEY', 0 

EXEC isoreseq '#cities', 'seq_num', 'state_name', 'city_name'
select * from #cities 

state_name           county_name          city_name            seq_num     
-------------------- -------------------- -------------------- ----------- 
ARIZONA              MARICOPA             PHOENIX              3
ARIZONA              MARICOPA             TEMPE                4
ARIZONA              MARICOPA             MESA                 2
ARIZONA              PIMA                 TUCSON               5
ARIZONA              PIMA                 GREEN VALLEY         1
NEW YORK             OSWEGO               PHOENIX              1
(6 row(s) affected)

EXEC isoreseq '#cities', 'seq_num', 'state_name', 'county_name', 'city_name'

select * from #cities 
state_name           county_name          city_name            seq_num     
-------------------- -------------------- -------------------- ----------- 
ARIZONA              MARICOPA             PHOENIX              2
ARIZONA              MARICOPA             TEMPE                3
ARIZONA              MARICOPA             MESA                 1
ARIZONA              PIMA                 TUCSON               2
ARIZONA              PIMA                 GREEN VALLEY         1
NEW YORK             OSWEGO               PHOENIX              1

(6 row(s) affected)


*/

Rate

Share

Share

Rate