It's hard to explain my question, but here is the issue. I have a table of 350k records. The table is filled with skus and other part information. There are many rows that have the same sku. For example there are 5 rows with the sku 45666. I need to put a unique identifier on each of these. e.g 45666~1, 45666~2, etc... The solution I've come up with was using a while loop in a while loop and just doing them one at a time like this
$i=0
while($i -le $array.length) {
$j=0
$mfp = $array.itemnumber[$i]
while($array.itemnumber[$i] = $mfp) {
Invoke-SQLCmd -ServerInstance $Server -Database $Database -Query "update whiproducts set number = $j"
$i++
$j++
}
}
What I would do after this is concat number with the sku when I select it. The problem here is this has been running for like 30 minutes which just won't work well with the way I'm going about this project. Is there some quick and easy way to do this I'm missing!?
Thanks for any help!
Give this query a try (please don't run this on production data before testing or backing up):
update t
set t.sku = t.sku + '~' + cast(t.RowNumber as varchar)
from (
select sku, row_number() over(partition by sku order by sku) as RowNumber
from whiproducts) t