0

I have constructed the following syntax but I keep getting an error of:

Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'INSERT'.

What do I need to change in my syntax so this executes appropriately?

    SET @SQL = N';WITH CTE AS
        (SELECT *                   
         FROM OPENQUERY(XXX.XXX.X.X, 
        ''Select 
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempid = jb.arempid
               WHERE xr.hiredate BETWEEN ('+ CHAR(39) + CHAR(39)
               + CAST(@startdate AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
               AND (' + CHAR(39) + CHAR(39) + CAST(@enddate AS NVARCHAR(500)) + CHAR(39)
            + CHAR(39) + N') GROUP BY cm.empName'')
            INSERT INTO Data (EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE';

            EXEC sp_executesql @sql;

This is actual syntax:

    ;WITH CTE AS
                    (SELECT *                   
                     FROM OPENQUERY(XXX.XXX.X.X, , 
        ''Select 
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempID = jb.arempID
               WHERE xr.hiredate BETWEEN BETWEEN (''01/01/2017'')
               AND (''01/31/2017'') GROUP BY cm.empname')
            INSERT INTO Data(EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE;

Insert is occurring on the local server. The table named Data exists on the same server I am executing the syntax on.

closed as off-topic by Erik Darling, mustaccio, Tom V, Michael Green, AMtwo Apr 25 '17 at 11:52

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Erik Darling, mustaccio, Tom V, Michael Green, AMtwo
If this question can be reworded to fit the rules in the help center, please edit the question.

1

Your dynamic SQL should end up looking like this.

   ;WITH CTE AS
         (SELECT * FROM OPENQUERY(XXX.XXX.X.X, 
        'Select 
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempID = jb.arempID
               WHERE xr.hiredate BETWEEN BETWEEN (''01/01/2017'')
               AND (''01/31/2017'') GROUP BY cm.arcustname'
            )--End OPENQUERY
        ) --END CTE
            INSERT INTO Data(EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE;

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