I have inherited a (Microsoft?) SQL database that wasn't very pristine in the original condition. You will find still some very strange things inside it that I am attempting to fix - one of these is sporadic ID records.

Within the accounts table, each entry includes a number known as accountID, that is recommended in a number of other tables (notes, equipment, etc. ). However , the amounts (for many random reason) - vary from about -100000 to +2000000 when you will find about only 7000 records.

Can there be worthwhile method to re-number them while altering corresponding amounts within the other tables? Inside my disposal I additionally have ColdFusion, so any factor that actually works with SQL and/or that I'll accept.

For surrogate secrets, they're intended to be meaningless, so unless of course you really were built with a database integrity problem (like there have been no foreign key contraints correctly defined) or perhaps your identity was approaching the utmost because of its datatype, I'd let them be and pursue another low hanging fruit that will convey more impact.

In cases like this, it may sound like "why" is really a better question than "how". The OP notes that there's an unusual problem that should be fixed but does not say why it's a problem. Could it be leading to problems? What positive impact would altering these amounts have? Unless of course you initially designed the machine and understand the key reason why the amount is within its current condition, you're taking a significant dangerous making changes such as this.

I'd speak with a cpa (or at best your financial people) before playing in anyway using the amounts within the accounts tables if this sounds like an economic application. The Table of accounts is extremely important to how money is reported. These IDs might have meaning you do not understand. Nobody puts in an adverse id unless of course they'd grounds. I'd on no account change that unless of course I understood why it had been negative to start with. You can truly ruin your tax confirming as well as other factor by looking into making an uneeded change.

You can most likely disable the foreign key associations (if you are capable of taking it offline temporarily) after which update the main secrets utilizing a script. I have used this update script before to alter values, and also you could pretty easily wrap this code inside a cursor to undergo the important thing values under consideration, 1 by 1, increase the arbitrary value for an incrementing value you are monitoring.

Browse the script here: http://vyaskn.tripod.com/sql_server_search_and_replace.htm

If you've just got a listing of tables which use the main key, you can setup a number of UPDATE claims running within your cursor, and you wouldn't want to use this script (which could be a little slow).

It's worth asking, though, the values appear from wack. Performs this database have values added and erased constantly? Would be the primary key values really arbitrary, or will they just seem to be, however they genuinely have meaning? Though I am all for bringing together, you'd have to make sure that there is no purpose to individuals values.

With ColdFusion this should not be considered a huge task, but it will likely be untidy you'll also find to become careful. One way you could utilize is always to script the database after which produce a completely new, blank table schema. Set the accountID being an identity area within the new database.

Then, using ColdFusion, write a question which will pull all the old account data and place them in to the new database 1 by 1. For every row, allow the new database assign a brand new ID. After each place, pull the brand new ID (using either @@IDENTITY or MAX(accountID)) and keep new ID and also the old ID together inside a temporary table which means you know which old IDs fit in with which new IDs.

Next, do this again with each one of the child tables. For every old ID, pull its child records and re-place them in to the new database while using new IDs. When the primary secrets around the child tables are fine, you are able to place them as-is or allow the server assign brand new ones when they don't matter.

Setting new IDs in position by crippling associations temporarily may go, but you could also encounter conflicts if among the records is designated an ID that's already getting used through the old data that could cause conflicts.