SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is it Possible to renumber a Identity Column?


Is it Possible to renumber a Identity Column?

Author
Message
cooperk
cooperk
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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
Michael Valentine Jones
Michael Valentine Jones
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5680 Visits: 11771
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search