October 9, 2009 at 2:33 PM (Programming Tips, SQL Server)
Tags: Row_No(), SQL Server
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',
Now how to update all existing records. Here is quick solution
SET ProductName = row_no
FROM ( SELECT ProductName,
+ ' ##' + CAST(ProductID AS NVARCHAR)
+ ' - '
+ CAST(( Row_Number() OVER ( ORDER BY ProductID ) ) AS NVARCHAR) + '#'
) AS row_no
I need to append it in certain format having some prefix ‘##’ etc, that part can be ignored in case.