2

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?

  • Whenever looking to troubleshoot dynamic SQL include the executed text. It appears you have a print statement at the end for the querytext. Knowing what that reads will help us determine what went wrong. – Shooter McGavin Oct 30 '16 at 17:02
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.

  • 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. – Michael Green Feb 8 '17 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.