Dynamic SQL UNPIVOT and putting results into existing table

Question

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.

Sample Data

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?

Answer 1

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.

• Michael Green: Truncating an existing table and creating a new table from scratch require different permissions. Be aware that the application's credential(s) will need this assigned, which may drag in other, unintended rights.