May 12, 2011 at 5:22 pm
I need to update employee id's in our HR database. The problem is there are a lot of primary and foreign keys in the tables in the database. I thought it would be best to drop all keys, update the data in the tables and re-create the keys.
Does anyone have a script for this or a better method?
May 12, 2011 at 6:00 pm
Update the client table first where the FK exist and then update the Parent Table with the corresponding Primary Key or Unique Constraint.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 12, 2011 at 6:01 pm
You may not want to drop the PK, FK's etc depending upon your situation...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 13, 2011 at 3:34 am
do you mean you want to update the primary key column which is used as a FK in other tables?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 13, 2011 at 3:48 am
Stringzz (5/12/2011)
I need to update employee id's in our HR database. The problem is there are a lot of primary and foreign keys in the tables in the database. I thought it would be best to drop all keys, update the data in the tables and re-create the keys.Does anyone have a script for this or a better method?
Why do you want to do this?
How are the employee id's currently assigned?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 13, 2011 at 9:17 am
colin.Leversuch-Roberts (5/13/2011)
do you mean you want to update the primary key column which is used as a FK in other tables?
Yes, I want to update the primary key that is a foreign key in other tables
May 13, 2011 at 9:22 am
Stringzz (5/13/2011)
Yes, I want to update the primary key that is a foreign key in other tables
Could you please provide the schema and dummy data?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 13, 2011 at 9:23 am
Why do you want to do this?
How are the employee id's currently assigned?
Employee ID's are currently loaded with SSIS package. However, company is going through merger and employee id's are changing
May 13, 2011 at 9:31 am
Stringzz (5/12/2011)
I need to update employee id's in our HR database. The problem is there are a lot of primary and foreign keys in the tables in the database. I thought it would be best to drop all keys, update the data in the tables and re-create the keys.Does anyone have a script for this or a better method?
Probably too late to help you, but the better method would be not to use a column with business meaning, like employee id or SSN, as a primary key.
A surrogate identity key would be a better choice, with the employee id as a unique constraint (alternate key). Then you would only have to update one row in one table for each employee.
May 14, 2011 at 7:51 am
FWIW, I would keep both in the same table at least for the time being. Then at least you can use the old/new keys to test that everything is happy and still working. (So I guess I'm saying to at least test everything in a non-production server.) Then you can do your updates pretty much in place on the post-merger StaffID and compare to the old version if necessary.
otherwise, if something goes wrong, you could get into a LOT of trouble, because things won't match up properly...
I would add the extra column, populate it, then TEST TEST TEST. When you are absolutely sure you don't need the old column anymore, you could back up that table and then delete. (Alternatively, create a table of (Old_ID, New_ID) and then you should be good to go.)
Just been bitten by mistakes like this in the past... remember the ounce of prevention!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply