Insert Into Statement With OpenQuery 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.
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;