Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to update none Identity Primary Key value

Author  Topic 

emyk
Yak Posting Veteran

57 Posts

Posted - 2014-08-29 : 10:22:20
I am working on a project to consolidate 3 distinct buildings name code(6 digit character called BUILDING_CODE Primary Key) into one building code. This is an effort to merge/move everyone who is currently coded under the three building codes into one building code. We currently have this column defined on 5 tables as primary key (none identity) in SQL Server 2005.
Out of the five tables one has two store procedure dependencies.

What would be the best practice to update the six digit BULDING_CODE column Primary Key value without causing any issues?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-29 : 12:48:36
You'll need to update the child tables first and then the parent table, assuming you have foreign keys in place. If you are updating all data and if there's tons of data in the tables, then you'll likely want to do this in batches (looping and updating every n rows, say 1000 rows). Depending on your business logic, you may need to do all 5 in a transaction for each batch (if applicable).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -