By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @Ids VARCHAR(MAX) = 'D,E,B,A,C';
DECLARE @Existing TABLE (
the_id VARCHAR(2),
SortRank INT,
OptionValue INT
);
INSERT INTO @Existing (the_Id, SortRank, OptionValue) VALUES
('A', 1, 1),
('B', 2, 1),
('C', 3, 3),
('D', 4, 2),
('E', 5, 3)
-- Storing existing values in table variable so we can re-insert records in new order without losing existing OptionValue values
DECLARE @New TABLE (
id int identity primary key,
the_id VARCHAR(2),
SortRank INT,
OptionValue INT
);
INSERT INTO @New (the_Id, SortRank, OptionValue)
SELECT I.the_Id, I.SortRank, E.OptionValue
FROM (SELECT value AS the_Id,
ROW_NUMBER() OVER (ORDER BY CHARINDEX(',' + s.value + ',', ',' + @ids + ',')) AS SortRank
FROM STRING_SPLIT(@Ids, ',') s
) I JOIN
@Existing E
ON E.the_Id = I.the_id
ORDER BY I.SortRank ASC;
SELECT *
FROM @New
ORDER BY id;
| id | the_id | SortRank | OptionValue |
|---|---|---|---|
| 1 | D | 1 | 2 |
| 2 | E | 2 | 3 |
| 3 | B | 3 | 1 |
| 4 | A | 4 | 1 |
| 5 | C | 5 | 3 |