Insert into table使用查询从其他表获取一些值

Question

我无法使用查询插入值。正确获取和创建数据。我只对"insert into table part“有问题。

我正在从一组函数和连接中获取数据，并尝试将结果插入到表Temp_ParentG_Sales_Total中。

当数据被正确获取时，insert into部分就是我遇到的问题。以下是我正在使用的查询：

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

这是因为您试图将脚本包含在INSERT语句中。

您必须将您的INSERT语句添加到顶部的SELECT语句中，该语句提供了需要插入到表中的列。

我猜下面是你最常问的问题。

    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