renumbering lines

Question

renumbering lines

psconsulting

SSC Eights!

Points: 944
More actions
June 15, 2004 at 2:40 pm

#152184

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

hoo-t SSChampion Points: 12713 More actions · Answer 1

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

Peter Larsson SSC Enthusiast Points: 192 More actions · Answer 2

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 3

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.

------------------------------
The Users are always right - when I'm not wrong!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)