Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Is it Possible to renumber a Identity Column? Expand / Collapse
Author
Message
Posted Thursday, March 28, 2013 9:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 17, 2013 10:36 AM
Points: 1, Visits: 7
I wrote this script to do exactly what you are asking on a project i inherited. Hope this helps...k

--Drop temporary tables (if they exists) If you run this more than once.
Use [myDB]
Drop table tmpReIdTable
Go

--Create the temporary table
Use [myDB]
CREATE TABLE tmpReIdTable(
[newID] [int] IDENTITY(1,1) NOT NULL,
oldID [int] NULL,
CONSTRAINT [newID] PRIMARY KEY CLUSTERED
( [newID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

--Insert into temp table from sourceTable using existing IDs
Use [myDB]
Insert into tmpReIdTable
(oldID)
select ID from sourceTable Order by WhateverYouWant

--Update sourceTable table using newID from temp table
Use [myDB]
Update sourceTable
set sourceTable.ID=tmpReIdTable.newID
from sourceTable inner join tmpReIdTable
on sourceTable.ID=tmpReIdTable.oldID
GO

Post #1436575
Posted Thursday, March 28, 2013 10:09 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:09 PM
Points: 3,108, Visits: 11,502
One solution would be to change the DATATYPE of the IDENTITY column to BIGINT.

You would have to reload the table, and you would have to change foreign key columns in referencing tables to BIGINT.

You would also have to change stored procedures or application code where there are temp tables or parameters for the identity column to BIGINT.





Post #1436590
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse