Link DAX OpenQuery Output to SQL Server Temp Table
If a column name has brackets, you need to escape them. You can see how if you use function QUOTENAME().

SELECT QUOTENAME('[awful column name]') 
-- Result: [[awful column name]]]


Note the extra closing brackets. You should try adding an extra pair of enclosing brackets together with an additional closing one for your column names that include brackets. 

Assuming that your problem is tabular_cte's columns:

UPDATE temp
SET temp.[Current Contract Count] = cte.[[Contract Count]]]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[[Copy of Service Zone Code]]] = temp.[ServiceZoneAlternateKey]