asked
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
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
