Recently in one of projects during testing I required to append the row numbers to each of its records in one of the tables in Sql Server.
The query to see the Row numbers is simple.
SELECT Row_Number() OVER ( ORDER BY ProductId ) AS 'RowNo',
ProductId,
ProductName
FROM Products
Now how to update all existing records. Here is quick solution
UPDATE Products
SET ProductName = row_no
FROM ( SELECT ProductName,
( ProductName
+ ' ##' + CAST(ProductID AS NVARCHAR)
+ ' - '
+ CAST(( Row_Number() OVER ( ORDER BY ProductID ) ) AS NVARCHAR) + '#'
) AS row_no
FROM Products
) Products
I need to append it in certain format having some prefix ‘##’ etc, that part can be ignored in case.
