band_start|band_end|zone1_charge|zone2_charge|...
0|0.5|0.51|1.23|...
0.5|1|0.58|1.30|...

zoneref|bandref|charge
1|1|0.51
1|2|0.58
2|1|1.23
2|2|1.30

FOR
SELECT [min], [max], eu1, eu2, eu3, eu4, eu5, eu6, eu7, eu8, eu9, eu10, eu11, eu12, eu13, eu14, eu15, eu16, eu17, eu18, eu19, air3, air4, air5, air6, air7, air8, air9, code, zoneref
FROM #imp_carrierpricing, zonematching
OPEN band
FETCH NEXT FROM band INTO @min, @max, @eu1, @eu2, @eu3, @eu4, @eu5, @eu6, @eu7, @eu8, @eu9, @eu10, @eu11, @eu12, @eu13, @eu14, @eu15, @eu16, @eu17, @eu18, @eu19, @air3, @air4, @air5, @air6, @air7, @air8, @air9, @zonecode, @zoneref
WHILE (@@FETCH_STATUS <> -1)
BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		-- all the above stuff is just to get the values from each band into the cursor variables
		-- we need to check if this band already exists
		SELECT @bandref = ref from deliverymethodband WHERE low = @min AND high = @max
		IF @bandref IS NULL
		BEGIN
			-- if it doesn't lets create a band for it
			--PRINT 'null'
			INSERT INTO deliverymethodband (code, description, type, low, high) SELECT CAST(@max * 2 AS INT), CAST(@min AS VARCHAR(5)) + '-' + CAST(@max AS VARCHAR(5)), 'W', @min, @max
		END

		DECLARE @charge MONEY
		-- let's case the values, not very nice to look at, but it works
		SET @charge = CASE 
			WHEN @zonecode = 'EU1' THEN @EU1
			WHEN @zonecode = 'EU2' THEN @EU2
			...
			WHEN @zonecode = 'AIR8' THEN @AIR8
			WHEN @zonecode = 'AIR9' THEN @AIR9
		END
		-- lets just make sure we have a charge...
		IF @charge is not null
		BEGIN
			-- work out if we're updating/inserting
			IF (
				EXISTS (
					SELECT * FROM deliverymethodzonecharge
					WHERE deliveryref = @deliveryref AND zoneref= @zoneref AND bandref = @bandref
					)
				)
			-- update
			BEGIN
				UPDATE deliverymethodzonecharge
				SET charge = @charge
				WHERE deliveryref = @deliveryref AND zoneref= @zoneref AND bandref = @bandref
			END
			ELSE
			--insert
			BEGIN
				INSERT INTO deliverymethodzonecharge (deliveryref, zoneref, bandref, charge)
				VALUES(@deliveryref, @zoneref, @bandref, @charge)
			END
		END
		SET @bandref = NULL
		SET @charge = NULL
		FETCH NEXT FROM band INTO @min, @max, @eu1, @eu2, @eu3, @eu4, @eu5, @eu6, @eu7, @eu8, @eu9, @eu10, @eu11, @eu12, @eu13, @eu14, @eu15, @eu16, @eu17, @eu18, @eu19, @air3, @air4, @air5, @air6, @air7, @air8, @air9, @zonecode, @zoneref
	END
END
CLOSE band
DEALLOCATE band

DELETE from deliverymethodzonecharge where zoneref in (select ref from deliveryzone where code like 'EU%' or code like 'AIR%')

INSERT INTO deliverymethodzonecharge (bandref, zoneref, charge, deliveryref)
SELECT *
FROM
(
SELECT d.ref as bandref, z.ref as zoneref, v.eu1 as charge, @deliveryref as deliveryref
FROM #vko_imp_carrierpricing v, deliverymethodband d, deliveryzone z
WHERE d.low = v.min AND d.high = v.max AND z.code = 'EU1'
union
SELECT d.ref as bandref, z.ref as zoneref, v.eu2 as charge, @deliveryref as deliveryref
FROM #vko_imp_carrierpricing v, deliverymethodband d, deliveryzone z
WHERE d.low = v.min AND d.high = v.max AND z.code = 'EU2'
union
...
SELECT d.ref as bandref, z.ref as zoneref, v.air8 as charge, @deliveryref as deliveryref
FROM #vko_imp_carrierpricing v, deliverymethodband d, deliveryzone z
WHERE d.low = v.min AND d.high = v.max AND z.code = 'AIR8'
union
SELECT d.ref as bandref, z.ref as zoneref, v.air9 as charge, @deliveryref as deliveryref
FROM #vko_imp_carrierpricing v, deliverymethodband d, deliveryzone z
WHERE d.low = v.min AND d.high = v.max AND z.code = 'AIR9'
)a