Update rows based on rownumber in SQL Server 2012

Question

Ive been given some data in a spreadsheet which will soon be going into an automated import so i cannot do any manual entry on the spreadsheet. The data basically has the following columns. Trayid, trayname, itemdescription and rownumber. I didnt build these tables myself or i would of built it differently but i have to stick to the format which is already set.

The Data that is being imported will look at followed.

Trayid | Trayname | ItemDescription | RowNumber
1        Tray 1     Product 1         1
                    Product 2         2
                    Product 3         3
                    Product 4         4
2        Tray 2     Product 1         1
                    Product 2         2
                    Product 3         3
                    Product 4         4
                    Product 5         5

What i need to do is update the trayid and trayname for each of the other rows following row 1, so for example it will look like.

Trayid | Trayname | ItemDescription | RowNumber
1        Tray 1     Product 1         1
1        Tray 1     Product 2         2
1        Tray 1     Product 3         3
1        Tray 1     Product 4         4
2        Tray 2     Product 1         1
2        Tray 2     Product 2         2
2        Tray 2     Product 3         3
2        Tray 2     Product 4         4
2        Tray 2     Product 5         5

Im guessing i need to use a curser or something but im not sure, i think it can be done by going down the rownumbers and stopping when it see's rownumber 1 again and then carrying on with the next trayid and trayname.

Sorry if what i need doesnt make sense, it was awkward to explain.

Answer 1

SQL tables have no inherent ordering. So you cannot depend on that. But, there is something that you can do:

• Define an identity column in the source table.
• Create a view on the source table that excludes the identity.
• Bulk insert into the view.

This will assign a sequential number to rows in the same order as the original data. Let's call this id. Then you can do your update by doing:

with toupdate (
      select t.*,
             max(TrayId) over (partition by grp) as new_TrayId,
             max(TrayName) over (partition by grp) as new_TrayName
      from (select t.*,
                   count(TrayId) over (order by id) as grp
            from t
           ) t
     )
update toupdate
    set TrayId = new_TrayId,
        TrayName = new_TrayName
    where TrayId is null;

The idea is to define groups of rows corresponding to each tray. The simple idea is to count the number of non-NULL values before any given row -- everything in a group will then have the same grp value. Window functions then spread the actual value through all rows in the group (using max()), and these values are used for the update.