Your "slow" problem is in using cursors. Absolutely no need in this case. Also, your query is wrong:
Code:
declare @id int
declare cur CURSOR LOCAL for
select id = ProductID from Product WHERE (Published = '0')
open cur
fetch next from cur into @id
while @@FETCH_STATUS = 0 BEGIN
UPDATE Productvariant SET Cost = SalePrice WHERE VariantID = @id;
UPDATE ProductVariant SET SalePrice = 0.00 WHERE VariantID = @id;
fetch next from cur into @id
END
close cur
deallocate cur
You are using Product.ProductID in the cursor to update the ProductVariant table based on the VariantID, so all of your records will get mixed up. You will need to revert to your backup before doing it again.
Code:
UPDATE pv SET Cost=SalePrice FROM ProductVariant pv JOIN Product p ON p.ProductID=pv.ProductID WHERE p.Published=0
UPDATE pv SET SalePrice=0 FROM ProductVariant pv JOIN Product p ON p.ProductID=pv.ProductID WHERE p.Published=0