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)
*/ 
