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',



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.

1 Comment

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: