Changing a SQL Server identity primary key to be sequential from 1
I have a SQL Server 2000 table that contains all our employee numbers, names and info about each employee.
After the company is sold, all the data will remain in place EXCEPT all the Employee Id numbers will be deleted, and will be recreated from 1 (and increasing by 1) based on everyone's original Date Of Hire (doh).
I can list the records in the desired order with:
SELECT IdNumber
FROM EmpTable
ORDER BY doh ASC
(The IdNumber is an identity primary key, so I'll have to temporarily turn that off... and then later turn it back on.) Nothing else is tied to that key column. This is a SINGLE table with everything in it.... that pertains to each employee. If 2 employees have the same DOH, it doesn't really matter which gets a lower ID number. But I guess I could always sub-sort on a secondary field like: Last Name
But how would I write those same records back to the same table, changing only the IdNumber column?
As a "second choice"... I could make a separate table with the new ID numbers... and then after everything is in there... and working... put the new table online.
As a "third choice"... I could use the 1 table... and make add a new field NewId, a put the newly created IDs there.
I guess there's many ways to do it... but I can't seem to figure out any of them.
试试这个SQL命令DBCC CHECKIDENT（yourtable，reseed，1）
假设您关闭了IdNumber列的主键和标识，
UPDATE EmpTable
SET EmpTable.IdNumber = orderTable.rowNo
FROM( SELECT ROW_NUMBER() OVER (ORDER BY doh ASC) as rowNo, IdNumber
FROM EmpTable) as orderTable
WHERE orderTable.IdNumber = EmpTable.IdNumber
To do this correctly without losing data integrity of the related tables the best thing to do is to script out (to make is easier to reset them later) and drop all FKs using the old id.
Add a new column for the new employee id. Rename the old column, old Id and give the new one the old name. Then using the oldid column, update the employee ID in all related tables to the new ID. Then add the FKs back in. This should be done with the database in single user mode and no one should be making any other changes to the database at the time it is done. You will probably want to drop and recreate all indexes (those should be scripted as well).
Incidentally you will need a plan for how you will handle it if two or more employees have the same Date of hire. This also needs to be done first ina dev environment and then all applications, reports, imports/exports need to be fully tested to ensure they still work. Generally something like this should take at least a month if not longer (depending on the complexity of the database) to set up and test thoroughly before moving to prod. This is a crtitcal task that cannot be done in a haphazard or slapdash way. Do not even think of doing it unless you have good backups that you know how to restore (and have proacticed doing so).
链接地址: http://www.djcxy.com/p/95674.html上一篇: 你应该多久重建一次索引？