September 20, 2002 at 10:09 am
I need to change all the employee nos in a database as result of merging departments. The employee number is a primary key linked to other tables. What is the best strategy to carry this out.
Thanks for your help.
September 20, 2002 at 10:34 am
If I understand this, you have an identity column (EmpID or something) in a table that is a PK. You need to change these values.
What you might want to do is start at the bottom and script out changes. So if you have some child table (EmpHours) that has a EmpID FK to the Emp table, script the update
update EmpHours
set EmpID = x
where EmpID = y
orsomething like that. Then script the change for the main table,
update Emp
set EmpID = x
where EmpID = y
of course you might want to remove the identity first. This is a table rebuild, but EM makes it easy.
I'd also script the drop and rebuild of the FK links (if they are there) because you will not be able to perform the update iwth the FK link in place.
The other solution is to "copy" the Emp table rows that need to change to their new values. Then update the lower tables and delete the old rows in the Emp table. However, this leaves more places for data to get messed up (IMHO).
Steve Jones
September 21, 2002 at 5:57 am
This looks a lot more complicated than I thought. As you rightly said, there are a lot of child tables having the EmpID as FK. May be I can create a composite key with the new ID as a new column. Or even create a new table with the new ID and link it to the old Employee Table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply