-- http://www.resquel.com/ssb
-- 2008-01-15

-- Create original table
CREATE TABLE OrderOfThings(
   Ident int IDENTITY(1,1) NOT NULL,
   OrderOfSteps bigint NOT NULL 
     CONSTRAINT df_OveridebleIdentity 
       DEFAULT (IDENT_CURRENT('OrderOfThings') * 10),
   OrderedSteps varchar(50) NOT NULL)
GO
-- Original table is populated
INSERT OrderOfThings (OrderedSteps) VALUES ('The 10th step.')
INSERT OrderOfThings (OrderedSteps) VALUES ('The 20th step.')
INSERT OrderOfThings (OrderedSteps) VALUES ('The 40th step.')
INSERT OrderOfThings (OrderedSteps) VALUES ('The 50th step.')
-- The forgotten steps are added.
INSERT OrderOfThings (OrderedSteps, OrderOfSteps) VALUES ('The 30th step.', 25) 
INSERT OrderOfThings (OrderedSteps, OrderOfSteps) VALUES ('The 35th step.', 27)
INSERT OrderOfThings (OrderedSteps, OrderOfSteps) VALUES ('The 36th step.', 28)
INSERT OrderOfThings (OrderedSteps, OrderOfSteps) VALUES ('The 38th step.', 29)
GO
SELECT * FROM OrderOfThings ORDER BY OrderOfSteps
-- See the problem if the the 37th step needs to be aadded to.
-- The OrderOfThings needs to be reorganized.
GO
-- Create "New" OrderOfThings
CREATE TABLE OrderOfThings_Tmp(
   Ident int IDENTITY(1,1) NOT NULL,
   OrderOfSteps bigint NOT NULL 
     CONSTRAINT df_OveridebleIdentity_Tmp 
		DEFAULT (IDENT_CURRENT('OrderOfThings_Tmp') * 10), -- Take care, reference the proper table for the default.
   OrderedSteps varchar(50) NOT NULL)
GO
-- Don't use, it will use the same identity for all records.
/*
INSERT OrderOfThings_Tmp(OrderedSteps)
	SELECT OrderedSteps FROM OrderOfThings ORDER BY OrderOfSteps
GO
	SELECT * FROM OrderOfThings_Tmp ORDER BY OrderOfSteps
GO
DROP TABLE OrderOfThings_Tmp 
GO
*/
--Fill based on cursor.
DECLARE @OS varchar(50)
DECLARE Reload_Cursor CURSOR FAST_FORWARD FOR 
SELECT OrderedSteps FROM OrderOfThings ORDER BY OrderOfSteps

OPEN Reload_Cursor

FETCH NEXT FROM Reload_Cursor INTO @OS
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT OrderOfThings_Tmp(OrderedSteps)
		VALUES (@OS)
    FETCH NEXT FROM Reload_Cursor INTO @OS
END

CLOSE Reload_Cursor
DEALLOCATE Reload_Cursor
GO
-- Check the cursor made the proper changes.
SELECT * FROM OrderOfThings_Tmp ORDER BY OrderOfSteps
GO
-- Drop the old table, rename the tmp-table and recreate the default.
DROP TABLE OrderOfThings
GO
EXEC sp_rename 'OrderOfThings_Tmp', 'OrderOfThings'
GO
ALTER TABLE OrderOfThings 
	DROP CONSTRAINT df_OveridebleIdentity_Tmp
GO
ALTER TABLE OrderOfThings 
	ADD CONSTRAINT df_OveridebleIdentity 
		DEFAULT (IDENT_CURRENT('OrderOfThings') * 10) FOR OrderOfSteps
GO