SQLTeam.com | Weblogs | Forums

Trying to resequence sequence field and add record

I have a field that is an int, but the sequence numbers and like 1,2,3,4. The code below needs to add the record (x) between 2 and 3
name SequenceNo
======== =========
d 1
g 2
b 3
f 4
a 5

name SequenceNo
======== =========
d 1
g 2
x 3
b 4
f 5
a 6

update c
set SequenceNo = y.NewSequenceNo
from (
select *, ROW_NUMBER() over (order by SequenceNo) [NewSequenceNo]
from (
select *
from tblTrkCustomField
select null, null, null, null, null, null, null, null, null, 2.5, null, null, null, null
) x
) y
join tblTrkCustomField c on c.Id = y.Id

insert tblTrkCustomField (Name, NameKey, Description, FieldTypeId, MinNumber, MaxNumber, MaxLength, Scale, SequenceNo, CanFilter, CanGroup, ReadOnly, Calculated) values
('Date Separation Pkg Mailed', 'dateseparationpkgmailed', '', 3, null, null, null, null, 3, 1, 1, null, null)

SET SequenceNo = SequenceNo +1
FROM tblTrkCustomField AS U
WHERE SequenceNo > 2


but if you do that every time you insert a row it will create a lot of LOG traffic, and will not scale well.

Alternative is to use floating point numbers for the SequenceNo - so insert the new row with SequenceNo = 2.5 (i.e. take the average of the [SequenceNo ] of the two adjacent rows)

UPDATE TableToModify SET SequenceNo+=1 WHERE SequenceNo>2;

INSERT TableToModify (Name, SequenceNo) VALUES('x', 3);