Update ID NUmber in Several SQL Tables

Gentlemen, I imported data from ACT into another Application that can't handle the non alphanumeric ID's. I need to replace these ID's with 22 digit Numeric ID's. Companies and Contacts for the Company exist in a Company Table and they share ID Numbers. There are 3800 distinct ID's in this table. If an ID exists in this table, it will exist in several other tables that will need updating. I used the Code below which seems to work but uses so much memory it crashes SSMS before it finishes. Any suggestions to optimize this? I know cursors use a lot of overhead but I'm not all that familiar with SQL and didn't see anothe way. Thanks


CREATE PROCEDURE dbo.SYS_tempsp_ReplaceClientId @OldClientId varchar( 24 ), @NewClientId varchar( 24 )
AS
BEGIN
DECLARE @Err    int
SET ROWCOUNT 0
SET NOCOUNT ON
BEGIN TRANSACTION
-- update clients
UPDATE dbo.Company SET Id = @NewId WHERE Id = @OldId
SELECT @Err = @@ERROR
-- update notes
IF @Err = 0 BEGIN
    UPDATE Notes SET Id = @NewId WHERE Id = @OldId
    SELECT @Err = @@ERROR
END
-- update documents
IF @Err = 0 BEGIN
    UPDATE dbo.Documents SET Id = @NewId WHERE Id = @OldId
    SELECT @Err = @@ERROR
END
-- update appointments
IF @Err = 0 BEGIN
    UPDATE dbo.Appointments SET Id = @NewId WHERE Id = @OldId
    SELECT @Err = @@ERROR
END
-- result
IF (@Err = 0)
    COMMIT
ELSE
    ROLLBACK
RETURN @Err
END
GO

Declare
@newid Varchar(24),
@oldid Varchar(24)
Declare IdReplace Cursor for
Select Distinct Id from dbo.Customers
Open IdReplace
Fetch Next from IdReplace into @OldId
While(@@FETCH_STATUS<> -1)
Begin
--Generate random 22 digit random number in the range indicated
Set @newid =  ABS(checksum(newid())%9999999999999999999999 + 1000000000000000000000)
Set Rowcount 0
Select Id from Customers where Id = @newid
While @@ROWCOUNT = 0
Begin
Exec  dbo.SYS_tempsp_ReplaceClientId @oldclientid,@newclientid
End
 Fetch Next from ClientIDReplace into @OldClientId
 End
 Close ClientIdReplace
 Deallocate ClientIdReplace
MaxSupportAsked:
Who is Participating?
 
SharathData EngineerCommented:
I tested with table variables. Remove the table variables and use your original table names.
select ID,convert(varchar(24),NULL) as New_ID into #Temp from Company union 
select ID,NULL from Notes union 
select ID,NULL from Documents union 
select ID,NULL from Appointments 

update #Temp set New_ID = ABS(checksum(newid())%9999999999999999999999 + 1000000000000000000000)

update c set c.ID = t.New_ID from Company c join #Temp t on c.ID = t.ID
update a set a.ID = t.New_ID from Appointments a join #Temp t on a.ID = t.ID
update n set n.ID = t.New_ID from Notes n join #Temp t on n.ID = n.ID
update d set d.ID = t.New_ID from Documents d join #Temp t on d.ID = t.ID

drop table #Temp

Open in new window

0
 
SharathData EngineerCommented:
You can try like this.
select ID,convert(varchar(24),NULL) as New_ID into #Temp from Company union 
select ID,NULL from Notes union 
select ID,NULL from Documents union 
select ID,NULL from Appointments 

update #Temp set New_ID = ABS(checksum(newid())%9999999999999999999999 + 1000000000000000000000)

update c set c.ID = t.New_ID from @Company c join #Temp t on c.ID = t.ID
update a set a.ID = t.New_ID from @Appointments a join #Temp t on a.ID = t.ID
update n set n.ID = t.New_ID from @Notes n join #Temp t on n.ID = n.ID
update d set d.ID = t.New_ID from @Documents d join #Temp t on d.ID = t.ID

drop table #Temp

Open in new window

0
 
MaxSupportAuthor Commented:
Thank you. I haven't had a chance to try this as yet but I will and will let you know.
0
 
mmr159Commented:
It appears that your backend handles alphanumeric fine, just the frontend is broken?

If this is the case, would it be possible to modify the backend to either add an intermediate table, joining the alphas with the numerics?  -or- Chang the table structure so that it holds numerics only, and update the populating/updating objects accordingly?

If there are only 3800 distinct IDs, then it seems inefficient to churn out gigantic keys like you are.  I haven't tested anything but I'm curious to know what's killing this, the successive updates or key generation.  Perhaps run a couple bench tests to find out, and report back.

Also, if newid is a number, declare the proper data type.
0
 
MaxSupportAuthor Commented:
Sharath, That worked great. Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.