June 15, 2004 at 2:40 pm
Anyone have a good idea on renumbering rows in a column that might have been misnumbered by an application. This is in a gigantic table and each case is isolated so I can't use a row count or anything like that.
Example:
Wrong Right
Col1 Col1
1 1
2 2
3 3
23 4
32 5
June 15, 2004 at 3:12 pm
I'm not completely sure what you are trying to achieve or what you are experiencing. I suspect that you are attempting to number the records in a sequential order as they are inserted, then reading them back with a select and not seeing the correct order????
If this is the case, its understandable. SQL Server does not store records sequentially, ie, there is no such thing as a "record number". Perhaps all you need to do is put an "ORDER BY" clause in your select?
select * from MyTable order by col1
Steve
June 16, 2004 at 1:59 am
CREATE TABLE #Temp
(
NewRowID INT IDENTITY (1, 1) NOT NULL,
OldRowID INT
)
INSERT INTO #Temp (OldRowID)
SELECT MyTable.Col1 FROM MyTable ORDER BY MyTable.Col1
UPDATE MyTable
SET MyTable.Col1 = #Temp.NewRowID
FROM MyTable, #Temp
WHERE MyTable.Col1 = #Temp.OldRowID
DROP TABLE #Temp
June 16, 2004 at 4:46 am
If the coulmn is not an identity column, you can write a cursor to populate a new colum incrementing each row by 1. You can then drop the original column and rename the new one to the original column name.
However, this is not a speedy method, but then again if it is a large table you are probabaly looking at a long running job whatever you do as well as somedowntime.
If it is an identity column, then the above example by Peter is probably the best method.
Alterntatively, dts the data out, truncate the table, run DBCC CHECKIDENT('yourtblname', RESEED, 1) and import the data back in again. You will need a fair bit of downtime for this method.
June 16, 2004 at 6:05 am
Regardless of which method you use to do the renumbering, if Col1 is an IDENTITY column, you would need to include the following line just before the update...
SET IDENTITY_INSERT tablename ON.
After the UPDATE, you would need to execute the following to return things to normal...
SET IDENTITY_INSERT tablename ON.
All that being said, I have to remind everyone that there may be other tables that store references to the table being renumbered. If that is true, those references will no longer work correctly. I don't recommend renumbering a table... there's always something "hidden" that will burn you, later.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply