asked on

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

Sharath S

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

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MaxSupport

ASKER

Thank you. I haven't had a chance to try this as yet but I will and will let you know.

mmr159

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.

MaxSupport

ASKER

Sharath, That worked great. Thank you