Sql-server – Dynamic SQL UNPIVOT and putting results into existing table

I know that there are a few questions out there on Dynamic SQL and Pivoting, but I'm unable to figure out the issue that I came across since I'm not familiar with Dynamic SQL/Pivoting.

So, I have the data as follows that contains the weekly sales.

The numbers you see as columns refer the weeks of a year, and the columns are populated once the data is updated, and the week numbers don't go beyond 53, which means we know how many columns are going to be returned.

I can use static unpivot to handle this one, but the reason why I'm using Dynamic SQL to pull this off is to work with something new that I'm not familiar with, and trying to learn more about it, and the other reason is to get some knowledge that I can use dynamic sql on our monthly sales data, since I don't know how many columns are going to be returned and how can I put the unknown number of columns returned of the dynamic sql into a local table.

I've made some research and tried to unpivot the data to normalize with the following query, and it seems to be working.

-- create the dynamic date columns string
declare @cols AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(column_name) 
                      FROM (
                            -- get columns as a list
                            select  TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
                            from    INFORMATION_SCHEMA.COLUMNS
                            where   TABLE_NAME = 'WEEKLY_SALES'
                            and     ORDINAL_POSITION not in (1,2,3,4,5)
                            ) cols
                      ORDER BY ORDINAL_POSITION                 
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''                            
                      )

-- Unpivot using dynamic sql
declare @sqlStr nvarchar(max)
set @sqlStr = N'
            select AREAS,COMPANY,PRODUCT,MARKET,SUBTERRITORIES, Col as WEEK, UNITS
            from(
                select AREAS,COMPANY,PRODUCT,MARKET,SUBTERRITORIES, ' + @cols + '
                from WEEKLY_SALES
            ) as cp
            unpivot
            (
                UNITS for Col in (' + @cols + ')
            ) as up'

exec sp_executesql @sqlStr

Now, I'm looking for a way to put the result of the above query into a local table. Could you please help me in doing this? The thing I'm going to do is, to truncate the table every week and insert the data with the updated weekly sales.

I've created a table where I will put the results into, and performed the following query, but it returns the error messages mentioned below.

   DECLARE
   @queryUpdate AS NVARCHAR(MAX),
   @queryInsert AS NVARCHAR(MAX);

SET @queryInsert = 'INSERT INTO dbo.WEEKLY_SALES_ (AREAS, COMPANY, PRODUCT, MARKET, SUBTERRITORIES, ' + @cols + ') 
                SELECT src.AREAS, src.COMPANY, src.PRODUCT, src.MARKET, src.SUBTERRITORIES ' + REPLACE(@cols, '[', 'src.[') + '
                FROM ('
             + @sqlStr
             + ') as src
                LEFT JOIN dbo.WEEKLY_SALES_ as dest
                   ON src.COMPANY = dest.COMPANY
                  AND src.AREAS = dest.AREAS
                  AND src.PRODUCT = dest.PRODUCT
                  AND src.MARKET = dest.MARKET
                  AND src.SUBTERRITORIES = dest.SUBTERRITORIES
                WHERE dest.COMPANY IS NULL;';

PRINT (@queryInsert);
EXECUTE (@queryInsert);

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'as'.

Is there anything else am I missing?