none
Making a column of non-sequential integers, sequential again – help please!

    Dotaz

  • Hi all,

    I have a question and hopefully someone can assist. Be gentle – I’m a novice.

    I have a table with an integer column called OrdinalPosition, which takes care of the order each entry appears on a web page.

    The problem is, that over time some of the rows have been deleted, so rather than running like this in the table…

    1

    2

    3

    4

    5

    6

    7

    8

    9

    10

    They run like this…

    1

    2

    5

    7

    8

    10

    So, now I want to do some ‘house-keeping’

    Using the example above I want this to run sequentially…

    1

    2

    3

    4

    5

    6

    So, in other words – how to I take some related, but non-sequential integers, and make them sequential again.

    I thought that perhaps using a temporary table to copy them out, re-order them and them copy them back somehow???

    Any advice most graciously received.

    Thanks.

    J

    11. května 2012 14:29

Odpovědi

  • Thanks for everyone’s feedback and ideas.

    So, I ended up getting some help from a colleague on this. Here’s the approach he took with this…

    ALTER PROCEDURE [Maintenance].[usp_ReSequenceExcelColumns](
    	@ExcelReportID			INT,
    	@PerformUpdate			BIT,		-- 0=No (i.e. test only), 1=Yes
    	@ExcelColumnIDToMove		INT=0,		-- Allows a newly added column id to be moved to a different ordinal position
    	@OrdinalPositionToMoveTo	INT=0)		-- Allows user to specify which position the column should be moved to
    AS
    BEGIN
    	CREATE TABLE #WorkTable1(
    		ID			INT IDENTITY,
    		ColumnID		INT,
    		ColumnName		NVARCHAR(500),
    		OldOrdinalPosition	INT)
    	INSERT
    		#WorkTable1(ColumnID,ColumnName,OldOrdinalPosition)
    	SELECT
    		ExcelColumnID,
    		ColumnName,
    		OrdinalPosition
    	FROM
    		Reports.ExcelColumn
    	WHERE
    		ExcelReportID = @ExcelReportID
    	ORDER BY
    		CASE
    			WHEN OrdinalPosition = 0 THEN 99999999		-- Ensure that any newly added columns go at the end by default
    			ELSE OrdinalPosition
    		END
    	CREATE TABLE #WorkTable2(
    		ID				INT IDENTITY,
    		ColumnID			INT,
    		ColumnName			NVARCHAR(500),
    		OldOrdinalPosition	INT)
    	INSERT
    		#WorkTable2(ColumnID,ColumnName,OldOrdinalPosition)
    	SELECT
    		ColumnID,
    		ColumnName,
    		OldOrdinalPosition
    	FROM
    		#WorkTable1
    	ORDER BY
    		CASE WHEN ColumnID = @ExcelColumnIDToMove THEN @OrdinalPositionToMoveTo ELSE ID END,
    		CASE WHEN ColumnID = @ExcelColumnIDToMove THEN 1 ELSE 2 END
    	DROP TABLE #WorkTable1
    	SELECT
    		ID AS NewOrdinalPosition,
    		OldOrdinalPosition,
    		ColumnID,
    		ColumnName
    	FROM 
    		#WorkTable2
    	IF @PerformUpdate = 1
    		UPDATE
    			Reports.ExcelColumn
    		SET
    			OrdinalPosition = WT.ID
    		FROM
    			#WorkTable2 WT
    		WHERE
    			WT.ColumnID = ExcelColumn.ExcelColumnID
    	DROP TABLE #WorkTable2
    END

    17. května 2012 11:03
  • Seems to be a lot of bloated and convoluted code there... Hard to maintain.
    Take a look at this sleeker implementation!
    ALTER PROCEDURE	Maintenance.usp_ReSequenceExcelColumns
    (
    	@ExcelReportID			INT,
    	@PerformUpdate			BIT,		-- 0 = No (i.e. test only), 1 = Yes
    	@ExcelColumnIDToMove		INT = 0,	-- Allows a newly added column id to be moved to a different ordinal position
    	@OrdinalPositionToMoveTo	INT = 0		-- Allows user to specify which position the column should be moved to
    )
    AS
    
    SET NOCOUNT ON
    
    IF @PerformUpdate <> 1
    	RETURN
    
    ;WITH cteTarget(OrdinalPosition, SeqID)
    AS (
    	SELECT	OrdinalPosition,
    		ROW_NUMBER() OVER (ORDER BY	CASE
    							WHEN ExcelColumnID = @ExcelColumnIDToMove THEN @OrdinalPositionToMoveTo
    							WHEN OrdinalPosition = 0 THEN 99999999
    							ELSE OrdinalPosition
    						END,
    						CASE
    							WHEN ExcelColumnID = @ExcelColumnIDToMove THEN 0
    							ELSE 1
    						END) AS SeqID
    	FROM	Reports.ExcelColumn
    	WHERE	ExcelReportID = @ExcelReportID
    )
    UPDATE	cteTarget
    SET	OrdinalPosition = SeqID


    N 56°04'39.26"
    E 12°55'05.63"

    17. května 2012 13:57

Všechny reakce

  • There is more than one things wrong with this situation - but rather than focusing on the issues, let's discuss a quick fix.

    What version on SQL Server are you running ? If its 2012, you can make use of a the sequence object to generate numbers in sequence and using the temp table approach, move the data and insert it in a re-ordered fashion.

    If not 2012, you can use the ROW_NUMBER() Function and still use the temp table approach. This can also be done via an UPDATE statement

    Finally, you can also use the temp table approach by adding an identity column to the temp table and then moving row back to the mail table such that the ordinalPosition column is based on the IDENTITY value.

    Hope this helps !


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com


    11. května 2012 14:36
  • Hi,

    How's this:

    ;WITH CTE AS
    	(
    	 SELECT 1 OrdinalPosition UNION
    	 SELECT 2 OrdinalPosition UNION
    	 SELECT 5 OrdinalPosition UNION
    	 SELECT 7 OrdinalPosition UNION
    	 SELECT 8 OrdinalPosition UNION
    	 SELECT 10 OrdinalPosition 
    	)
    	
    
    UPDATE D
    SET OrdinalPosition = CorrectedOrdinal
    FROM (
    	  SELECT OrdinalPosition, 
    		     ROW_NUMBER() OVER(ORDER BY OrdinalPosition) CorrectedOrdinal
    	  FROM CTE
    	 ) D



    Zach Stagers - http://www.scratchbox.co.uk
    Remember to Mark as Answer and Vote as Helpful

    11. května 2012 14:36
  • First, why do you worry about gaps in the sequence at all?

    Second, your suggestion could work if there are no foreign key-constraints.
    Third, read this article for a starter http://weblogs.sqlteam.com/peterl/archive/2009/03/10/How-to-efficiently-reuse-gaps-in-identity-column.aspx


    N 56°04'39.26"
    E 12°55'05.63"

    11. května 2012 14:37
  • Thanks for everyone’s feedback and ideas.

    So, I ended up getting some help from a colleague on this. Here’s the approach he took with this…

    ALTER PROCEDURE [Maintenance].[usp_ReSequenceExcelColumns](
    	@ExcelReportID			INT,
    	@PerformUpdate			BIT,		-- 0=No (i.e. test only), 1=Yes
    	@ExcelColumnIDToMove		INT=0,		-- Allows a newly added column id to be moved to a different ordinal position
    	@OrdinalPositionToMoveTo	INT=0)		-- Allows user to specify which position the column should be moved to
    AS
    BEGIN
    	CREATE TABLE #WorkTable1(
    		ID			INT IDENTITY,
    		ColumnID		INT,
    		ColumnName		NVARCHAR(500),
    		OldOrdinalPosition	INT)
    	INSERT
    		#WorkTable1(ColumnID,ColumnName,OldOrdinalPosition)
    	SELECT
    		ExcelColumnID,
    		ColumnName,
    		OrdinalPosition
    	FROM
    		Reports.ExcelColumn
    	WHERE
    		ExcelReportID = @ExcelReportID
    	ORDER BY
    		CASE
    			WHEN OrdinalPosition = 0 THEN 99999999		-- Ensure that any newly added columns go at the end by default
    			ELSE OrdinalPosition
    		END
    	CREATE TABLE #WorkTable2(
    		ID				INT IDENTITY,
    		ColumnID			INT,
    		ColumnName			NVARCHAR(500),
    		OldOrdinalPosition	INT)
    	INSERT
    		#WorkTable2(ColumnID,ColumnName,OldOrdinalPosition)
    	SELECT
    		ColumnID,
    		ColumnName,
    		OldOrdinalPosition
    	FROM
    		#WorkTable1
    	ORDER BY
    		CASE WHEN ColumnID = @ExcelColumnIDToMove THEN @OrdinalPositionToMoveTo ELSE ID END,
    		CASE WHEN ColumnID = @ExcelColumnIDToMove THEN 1 ELSE 2 END
    	DROP TABLE #WorkTable1
    	SELECT
    		ID AS NewOrdinalPosition,
    		OldOrdinalPosition,
    		ColumnID,
    		ColumnName
    	FROM 
    		#WorkTable2
    	IF @PerformUpdate = 1
    		UPDATE
    			Reports.ExcelColumn
    		SET
    			OrdinalPosition = WT.ID
    		FROM
    			#WorkTable2 WT
    		WHERE
    			WT.ColumnID = ExcelColumn.ExcelColumnID
    	DROP TABLE #WorkTable2
    END

    17. května 2012 11:03
  • Seems to be a lot of bloated and convoluted code there... Hard to maintain.
    Take a look at this sleeker implementation!
    ALTER PROCEDURE	Maintenance.usp_ReSequenceExcelColumns
    (
    	@ExcelReportID			INT,
    	@PerformUpdate			BIT,		-- 0 = No (i.e. test only), 1 = Yes
    	@ExcelColumnIDToMove		INT = 0,	-- Allows a newly added column id to be moved to a different ordinal position
    	@OrdinalPositionToMoveTo	INT = 0		-- Allows user to specify which position the column should be moved to
    )
    AS
    
    SET NOCOUNT ON
    
    IF @PerformUpdate <> 1
    	RETURN
    
    ;WITH cteTarget(OrdinalPosition, SeqID)
    AS (
    	SELECT	OrdinalPosition,
    		ROW_NUMBER() OVER (ORDER BY	CASE
    							WHEN ExcelColumnID = @ExcelColumnIDToMove THEN @OrdinalPositionToMoveTo
    							WHEN OrdinalPosition = 0 THEN 99999999
    							ELSE OrdinalPosition
    						END,
    						CASE
    							WHEN ExcelColumnID = @ExcelColumnIDToMove THEN 0
    							ELSE 1
    						END) AS SeqID
    	FROM	Reports.ExcelColumn
    	WHERE	ExcelReportID = @ExcelReportID
    )
    UPDATE	cteTarget
    SET	OrdinalPosition = SeqID


    N 56°04'39.26"
    E 12°55'05.63"

    17. května 2012 13:57