Insert into table using queries used to fetch some values from other tables

Question

I am unable to insert values using the query. Data is fetched and created correctly. I am only having issues with the "insert into table part"

I am Fetching data from a set of functions and joins and trying to insert the result into the table Temp_ParentG_Sales_Total.

While the data is getting fetched correctly, the insert into part is what I am having problems with. Following is the query I am using:

insert into MDM_STAT.dbo.Temp_ParentG_Sales_Totals (PARENT_DESCRIPTION, Total_Sales, Ranking)
select ABALPH, TOTAL, RANKING
from(  --- the query from this part to the bottom is working correctly.

USE MDM_STAT
DECLARE @RUNMONTH INT;
DECLARE @RUNYEAR INT;
DECLARE @PERIOD INT;
DECLARE @FISCALRUNYEAR INT;
DECLARE @FISCALRUNYEAR_BEGIN INT;
DECLARE @RANKING_TYPE VARCHAR(100);
DECLARE @RANKING_USE VARCHAR(100);
DECLARE @AUPPER DECIMAL(12,2);
DECLARE @ALOWER DECIMAL(12,2);
DECLARE @BUPPER DECIMAL(12,2);
DECLARE @BLOWER DECIMAL(12,2);
DECLARE @CUPPER DECIMAL(12,2);
DECLARE @CLOWER DECIMAL(12,2);
DECLARE @DUPPER DECIMAL(12,2);
DECLARE @DLOWER DECIMAL(12,2);
SET @RANKING_TYPE = 'CUSTOMER SALES';
SET @RANKING_USE='CUSTOMER SALES RANKING 8_4_2016';
SET @ALOWER =(SELECT LOWER_LIMIT FROM dbo.XREF_ABC_RANKING_RULES WHERE  RANKS='A'AND RANKING_TYPE= @RANKING_TYPE AND RANKING_USE = @RANKING_USE);
SET @BUPPER =(SELECT UPPER_LIMIT FROM dbo.XREF_ABC_RANKING_RULES WHERE  RANKS='B'AND RANKING_TYPE= @RANKING_TYPE AND RANKING_USE = @RANKING_USE);
SET @BLOWER =(SELECT LOWER_LIMIT FROM dbo.XREF_ABC_RANKING_RULES WHERE  RANKS='B'AND RANKING_TYPE= @RANKING_TYPE AND RANKING_USE = @RANKING_USE);
SET @CUPPER =(SELECT UPPER_LIMIT FROM dbo.XREF_ABC_RANKING_RULES WHERE  RANKS='C'AND RANKING_TYPE= @RANKING_TYPE AND RANKING_USE = @RANKING_USE);
SET @CLOWER =(SELECT LOWER_LIMIT FROM dbo.XREF_ABC_RANKING_RULES WHERE  RANKS='C'AND RANKING_TYPE= @RANKING_TYPE AND RANKING_USE = @RANKING_USE);
SET @DUPPER =(SELECT UPPER_LIMIT FROM dbo.XREF_ABC_RANKING_RULES WHERE  RANKS='D'AND RANKING_TYPE= @RANKING_TYPE AND RANKING_USE = @RANKING_USE);
SET @RUNMONTH=MONTH(GETDATE());
SET @RUNYEAR=YEAR(GETDATE());
SET @PERIOD=
      CASE 
      WHEN @RUNMONTH>3 THEN (@RUNMONTH-3)
      ELSE 9+@RUNMONTH
    END
    ;
SET @FISCALRUNYEAR=
      CASE
      WHEN @RUNMONTH>3 THEN @RUNYEAR
      ELSE @RUNYEAR-1
      END
      ;
SET @FISCALRUNYEAR_BEGIN=
      CASE
      WHEN @PERIOD=12 THEN @FISCALRUNYEAR
      ELSE @FISCALRUNYEAR-1
      END
      ;

SELECT F.ABALPH,F.TOTAL, RANKING=
CASE
WHEN F.TOTAL >@ALOWER THEN 'A'
WHEN F.TOTAL >@BLOWER AND F.TOTAL<=@BUPPER THEN 'B'
WHEN F.TOTAL >@CLOWER AND F.TOTAL<=@CUPPER THEN 'C'
ELSE 'D'
END
FROM
(
select AB.ABALPH, C.Total from JDE_TEST.TESTDTA.F0101 AB
right join
(
select Sum(B.TOTAL) as Total,G.MAPA8 from JDE_TEST.TESTDTA.F0150 G 
left join 
(
--DETERMINING 12 MONTHS SALE PER CUSTOMER
SELECT A.CUSTOMER, SUM( A.SALES) AS TOTAL FROM 
(
SELECT S.CUSTOMER, S.SALESYEAR, S.SALES, S.PERIOD FROM 
(
SELECT DISTINCT CUSTOMER,SALESYEAR,SUM(CAST(Dollar01 AS DECIMAL(11,2))) AS SALES, '1' AS PERIOD
FROM sds.SMarginText GROUP BY CUSTOMER,SALESYEAR
UNION 
SELECT DISTINCT CUSTOMER,SALESYEAR,SUM(CAST(Dollar02 AS DECIMAL(11,2))) AS SALES, '2' AS PERIOD
FROM sds.SMarginText GROUP BY CUSTOMER,SALESYEAR
) AS S
WHERE S.SalesYear=@FISCALRUNYEAR
AND S.PERIOD<=@PERIOD
UNION
SELECT T.CUSTOMER, T.SALESYEAR, T.SALES, T.PERIOD FROM 
(
SELECT DISTINCT CUSTOMER,SALESYEAR,SUM(CAST(Dollar01 AS DECIMAL(11,2))) AS SALES, '1' AS PERIOD
FROM sds.SMarginText GROUP BY CUSTOMER,SALESYEAR
UNION 
SELECT DISTINCT CUSTOMER,SALESYEAR,SUM(CAST(Dollar02 AS DECIMAL(11,2))) AS SALES, '2' AS PERIOD
FROM sds.SMarginText GROUP BY CUSTOMER,SALESYEAR
) AS T
WHERE T.SalesYear=@FISCALRUNYEAR_BEGIN
AND T.PERIOD>@PERIOD
)
AS A
GROUP BY A.CUSTOMER
) AS B
on cast(B.Customer as int) = G.MAAN8
where B.Customer <> 'Unspec'
Group By G.MAPA8
) As C
on C.MAPA8 = AB.ABAN8
) AS F
order by F.Total ASC)

Answer 1

This happens because you are trying to enclose a script inside your INSERT statement.

You have to add your INSERT statement right at the top SELECT statement that gives your necessary columns to insert into your table.

I guess below is your top query.

    insert into MDM_STAT.dbo.Temp_ParentG_Sales_Totals 
    (PARENT_DESCRIPTION, Total_Sales, Ranking)

    SELECT F.ABALPH,F.TOTAL, RANKING=
    CASE
    WHEN F.TOTAL >@ALOWER THEN 'A'
    WHEN F.TOTAL >@BLOWER AND F.TOTAL<=@BUPPER THEN 'B'
    WHEN F.TOTAL >@CLOWER AND F.TOTAL<=@CUPPER THEN 'C'
    ELSE 'D'
    --suppressed your other part of query
    where B.Customer <> 'Unspec'
    Group By G.MAPA8
    ) As C
    on C.MAPA8 = AB.ABAN8
    ) AS F
    order by F.Total ASC