Need help updating a field with a record number 2

[cal555]

Hi,
I have a table that has a field that holds a sequential order number in it, that is updated when a new record is added by a VB app. So for example, if the total current records count is a 100, and the app is adding one more, it will find the current number and add one to it; and insert it in this field. Before, I go on I should mention that they do not want this to be an Identity field. And normally this works great, but for reasons that would be too long to go into here, about 60 records got zeros put in; and they want me to the correct sequential number in each record. They thought that if I looked at the record number in query Analyzer I could use this, but I beleive that SQL Server orders it differently each time, if I rember right. Is there away to number the records?

 

[bborissov]

There is no such an animal like Record number in SQL Server ) SQL Server uses free space (when you delete some record) to write new one there, so if you want to number records somehow you must decide what order you want. Here one example, how you can number the records:

CREATE TABLE #test (recNo int NULL, PK int)
INSERT INTO #test VALUES (0, 1)
INSERT INTO #test VALUES (0, 2)
INSERT INTO #test VALUES (0, 5)
INSERT INTO #test VALUES (0, 10)
INSERT INTO #test VALUES (0, 3)
INSERT INTO #test VALUES (0, 4)

CREATE TABLE #test1 (RecNo Int IDENTITY(1,1), OtherTableFK int)
INSERT INTO #test1 (OtherTableFK)
SELECT PK FROM #test ORDER BY Pk

SELECT * from #test1

UPDATE #test SET recNo = #test1.RecNo
FROM #test
INNER JOIN #test1 ON #test.PK = #test1.OtherTableFK

SELECT * from #test
drop table #test
drop table #test1

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[mrdenny]

You'll need to write a cursor to update the fields that have the incorrect number in them.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[cal555]

Thank you both for your help, I really appreciate it.