filling/reordering the table data or fixing missing IDs issue

Posted by Bandi under Sql Server category on | Points: 40 | Views : 746
Here, the below table data is missing some IDs in the column named as 'col'.. To arrange the table data for sequential order, we can use the below code snippet....
--Sample Data for table test123
Create table test123 (col int, col2 char(1))
insert into test123
SELECT 1, 'a' union all
SELECT 2, 'b' union all
SELECT 3, 'c' union all
SELECT 4, 'd' union all
SELECT 6, 'e' union all
SELECT 10, 'f' union all
SELECT 11, 'g'

--This is the code snippet used for re-filling missing IDs
;With cte AS(SELECT col, ROW_NUMBER() over (ORDER BY col) RN FROM test123)
SET col = RN
SELECT * FROM test123
DROP TABLE test123

Comments or Responses

Login to post response