Changing ID globally ...

Hi
We have a help desk application with MS SQL Server backend which is at least 10 years old. This application mainly has 2 tables -
1. One for storing customer ID (Cust_Profile Table) and th
2. Other one for storing all the call details pertaining to customers.
Apart from these two main tables, there are other tables for storing information like configuration, history etc related to the customer
My requirement is - I want to change the customer ID globally across the system. But I am not too sure as to how to proceed. Any suggestions/ideas welcome
Thanks in advance
HarishHarish,
Review the following psuedocode use the following as guideline only...do not
do this directly on the production system and please test it on the staging
or dev env.
-- NO IDENTITY PROPERTY
Pause access to the database from the Application
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
UPDATE table1 ..
Check for Errors...rollback if needed.
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
-- YES IDENTITY PROPERTY
Check if the Customer ID is of an Identity property
Pause access to the database from the Application
If it is of Identity property you can not Update you have to write a
migration utility
You may have to Create a shadow table with same schema as Customer Profile
(Without Identity Property on ID column)
Copy Data from Base Table (Original) to Shadow Table
Update the Value (Old ID - New ID) in the Shadow Table
Drop any Foreign Keys between tables binding the Customer Key
Begin transaction
DELETE Base Table (Original) -- Use Delete instead of Truncate for
Rollback purposes
Check for Errors...rollback if needed.
SET IDENTITY_INSERT Base Table ON
INSERT Base Table (Collist) SELECT Collist From Shadow Table
SET IDENTITY_INSERT Base Table OFF
UPDATE table2...
Check for Errors...rollback if needed
Commit Transaction
Resume Access to the database from the Application
--
HTH
Satish Balusa
"Harish Mohanbabu" <anonymous@.discussions.microsoft.com> wrote in message
news:248AB598-83B4-4611-92E3-BE336DA65170@.microsoft.com...
> Hi,
> We have a help desk application with MS SQL Server backend which is at
least 10 years old. This application mainly has 2 tables -
> 1. One for storing customer ID (Cust_Profile Table) and the
> 2. Other one for storing all the call details pertaining to customers.
> Apart from these two main tables, there are other tables for storing
information like configuration, history etc related to the customer.
> My requirement is - I want to change the customer ID globally across the
system. But I am not too sure as to how to proceed. Any suggestions/ideas
welcome.
> Thanks in advance,
> Harish