I am converting data from a 3rd party provider into an inhouse SQL Server database. The incoming table has data which looks like this:

ID   Element1    Element2    Element3
-------------------------------------
 1      43582       1           7
 2      46852       2           6
 3      46852       4           4
 4      47895       2           9

I have a script which executes an

Insert into.... 
    Select..... 
    From......

which correctly imports the data from the source table properly except for 1 element in the destination table.

When I run my script, the resulting table looks like this:

ID    Location   LocationCounter   Drawer1   Drawer2
----------------------------------------------------
 1      43582          NULL          1         7
 2      46852          NULL          2         6
 3      46852          NULL          4         4
 4      47895          NULL          2         9

I need to modify my script to populate the LocationCounter column. I can default the value to 1 but in the case of location 46852, the first record inserted needs to have a LocationCounter value of 1 and the second record inserted needs to have a LocationCounter value of 2.

Any suggestions on how to accomplish this is greatly appreciated.