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 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 informa
tion like configuration, history etc related to the customer.
My requirement is - I want to change the customer ID globally across the sys
tem. But I am not too sure as to how to proceed. Any suggestions/ideas welc
ome.
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...

quote:

---

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

quote:

---

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

quote:

---

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

quote:

---

> Thanks in advance,
> Harish

---