Dynamic SQL UNPIVOT and putting results into existing table
Take away the text INSERT INTO dbo.WEEKLY_SALES... and everything after it on that line. Then change SELECT src.AREAS to:

SELECT INTO dbo.WEEKLY_SALES 
src.AREAS, src.COMPANY ...etc


Also, before your SET @query statement, add:

DROP TABLE dbo.WEEKLY_SALES


That way, when it runs, it will drop yesterday's values (the entire table definition as well).  Then the rest of the statement will create and load the table on the fly.

Honestly, if you're going to truncate each time anyways, you might want to consider creating the table dynamically using Select into. Basically just drop the table first, then add the into [table name] after the select keyword.  In terms of straight easy coding, that's as simple as it gets. It won't give you the best performance on reading the table though because there won't be any indexes. That's really the only downside.