Append Row Number to your table records

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.