May 29, 2010 at 8:13 am
Greetz All,
TableA
id integer Primary Key auto increment
TableB
id integer Primary Key auto increment
TableA.id foreign Key
Table A's primary auto incrementing key has grown to 2,147,483,645 which, as you know, is 2 away from the maximum Int storage value . It actually begins as a very large negative number! Previous developers must have done something weird because there are only about 16000 records in the tables. The keys are all out of sequence and the whold thing is a logical mess SO before I release this into the wild I want to reset the key values on this Table A and it's relation in Table B.
Any ideas on an efficient way to do this? I changed the columns from Int to Bigint when I imported them from Access but would like to fix the issue and reset the column to an Int in order to be greener 🙂 or at least to make more sense.
CREATE TABLE [dbo].[TableA](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[USDANumber] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[InspectionDate] [datetime] NULL,
[ReceivedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FileLocation] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Display] [bit] NULL CONSTRAINT [DF_USDAInspections_Display] DEFAULT ((1)),
CONSTRAINT [PK_USDAInspections] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[TableB](
[id] [int] IDENTITY(1,1) NOT NULL,
[requestid] [Bigint] NOT NULL,
[comments] [varchar](3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[externalnotes] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[internalnotes] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[healthnotes] [varchar](3000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[breeder] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[breederislicensed] [bit] NULL,
[broker] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[brokerislicensed] [bit] NULL,
[TableA_FOREIGN_KEY] [int] NULL,
[status] [tinyint] NOT NULL CONSTRAINT [DF_Report_status] DEFAULT ((1)),
[SentDate] [datetime] NULL,
CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
May 29, 2010 at 10:21 am
I think I might have a solution. SQL works best with sets but I don't see how to avoid looping through a table to accomplish this. If you can think of something better or offer any improvements to what I have I like to learn.
To start I'd turn off the identity auto increment on TableA and then run the following sql
ALTER TABLE TableA ADD Processed int not null default 0
Declare @Id int
Declare @ctr int
set @ctr = 1
While (Select Count(*) From TableA Where Processed = 0) > 0
Begin
Select Top 1 @Id = Id From TableA
Update TableB set TableA_FOREIGN_KEY = @ctr where TableA_FOREIGN_KEY = @Id
Update TableA Set Processed = 1 Where Id = @Id
Update TableA SET id = @ctr Where Id = @Id
SET @ctr = @ctr + 1
End
Perhaps a bit crude but likeable?
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
May 29, 2010 at 11:16 am
The problem becomes a LOT simpler if you use a separate temp table to do the renumbering.
Select Id as oldID, Identity(int,1,1) as newID
into #TempTableA
from TableA
order by ID
update TableA
set ID=NewID
from TableA join #TempTableA on ID=oldID
Then you can reseed the identity column and get your values back.
Still - you still need to figure out what causes 16000 rows to span 2.1 Billion values. If it happened once, it's likely to happen again......
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2010 at 12:52 pm
Thanks for your reply. Your solution looks more efficient than mine but I I'm not sure about the second half:
update TableA
set ID=NewID
from TableA join #TempTableA on ID=oldID
Am I updating TableA from itself? What about updating TableB which has TableA's ID as a foreign key..I guess I would run this??
update TableB
set ID=NewID
from TableA join #TempTableA on ID=NewID
I've resolved the issue with the ID so that won't happen again. The old system was a botch job and I suspect the developers (several over the years) did alot of testing and just never saw the need to fix the side effect of their testing.
Thanks again for your reply!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
May 29, 2010 at 1:42 pm
no - the temp table is a copy, and holds the "correlation" between the old and the new. The join is there to imply tell SQL server which value goes to which row
To recap:
Select
Id as oldID, --this is the old value with all of the gaps
Identity(int,1,1) as newID --this is the new value with NO gaps
into #TempTableA --makes a copy of the TableA key's into a new table
from TableA
order by ID
Update TableA --which of the two tables is actually being updated
set ID = NewID -- ID is the column in TableA, NewID is from the temp table
from
tableA join #TempTableA -- so you're starting to set up the relationship
on TableA.ID = #TempTableA.oldID
Of course - the above wouldn't work if there's a foreign key (the update would fail). You will need to disable the foreign FIRST.
Once you update TableA, you can update TableA in the very same way:
Update TableB
set WhateverColumninBWhichIsTheFK = NewID
from TableB
join #tempTableA on tableB.WhateverColumninBWhichIsTheFK = #TempTableA.oldID
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2010 at 4:14 pm
Thanks for clearing that up for me. I get it now. I'll give it a whirl.
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply