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
want.............
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
union
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)