x

How to Change ID Numbers in several 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
more ▼

asked Mar 28, 2011 at 01:54 PM in Default

avatar image

Vienna
11 1 1 1

What's the definition of SYS_tempsp_ReplaceClientID?

Mar 28, 2011 at 03:09 PM ThomasRushton ♦♦
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You can try updating your records in one set-based operation per table instead of using a cursor loop:

 declare @temp_list table
 (
     OldID varchar(22) primary key clustered,
     TheNewID decimal(22, 0)
 );
 
 insert into @temp_list
 select distinct 
     ID, abs(checksum(newid()) % 9999999999999999999999 + 
     1000000000000000000000)
     from dbo.Customers;
     
 begin tran;
 
 -- update clients
 update c
 set ID = list.TheNewID
     from dbo.Company c inner join @temp_list list
         on c.ID = list.OldID;
         
 -- update notes
 update n
 set ID = list.TheNewID
     from dbo.Notes n inner join @temp_list list
         on n.ID = list.OldID;
         
 -- update documents
 update d
 set ID = list.TheNewID
     from dbo.Documents d inner join @temp_list list
         on d.ID = list.OldID;
         
 -- update appointments
 update a
 set ID = list.TheNewID
     from dbo.Appointments a inner join @temp_list list
         on a.ID = list.OldID;
 
 commit tran;
 go

Oleg

more ▼

answered Mar 28, 2011 at 04:35 PM

avatar image

Oleg
19.8k 3 7 28

Oleg,

Thank you, that worked just right!

Mar 29, 2011 at 08:10 AM Vienna
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1183
x457
x70

asked: Mar 28, 2011 at 01:54 PM

Seen: 2087 times

Last Updated: Mar 28, 2011 at 02:23 PM

Copyright 2018 Redgate Software. Privacy Policy