sql-server - 将记录从 CTE 插入到表中

Question

我正在使用 SQL Server 2008。为了获取一些行，我在存储过程中使用 CTE。

;WITH
CTE AS (
    SELECT   BrokerId ,
                    RankId ,
                    BrokerName ,
                    RankName ,
                    BrokerCode ,
                    IntroducerCode ,
                    CscName ,
                    MAX(SIP) AS SIP ,
                    MAX(Fresh) AS Fresh ,
                    MAX(FY) AS FY ,
                    MAX(SY) AS SY ,
                    MAX(TY) AS TY ,
                    CscId ,
                    Promotive ,
                    NoOfPromotive ,
                    PlanTypeName ,
                    PlanYear
     FROM @tmp
     GROUP BY BrokerId ,
                    RankId ,
                    BrokerName ,
                    RankName ,
                    BrokerCode ,
                    IntroducerCode ,
                    CscName ,
                    CscId ,
                    Promotive ,
                    NoOfPromotive ,
                    PlanTypeName ,
                    PlanYear
)
SELECT  BrokerId ,
        RankId ,
        BrokerName ,
        RankName ,
        BrokerCode ,
        IntroducerCode ,
        CscName ,
        SUM(SIP) AS 'SIP' ,
        SUM(Fresh) AS 'Fresh' ,
        SUM(FY) AS 'FY' ,
        SUM(SY) AS 'SY' ,
        SUM(TY) AS 'TY' ,
        Promotive ,
        Total = ISNULL(( SUM(SIP) ), 0) + ISNULL(( SUM(Fresh) ), 0)
        + ISNULL(( SUM(FY) ), 0) + ISNULL(( SUM(SY) ), 0)
        + ISNULL(( SUM(TY) ), 0) ,
        NoOfPromotive ,
        PlanTypeName ,
        PlanYear ,
        CscId
FROM CTE
GROUP BY BrokerId ,
        RankId ,
        BrokerName ,
        RankName ,
        BrokerCode ,
        IntroducerCode ,
        CscName ,
        Promotive ,
        NoOfPromotive ,
        PlanTypeName ,
        PlanYear ,
        CscId
ORDER BY PlanTypeName 

它给了我正确的数据。现在我想将该数据插入到表中。我尝试过:

 INSERT INTO MyTable
    ( BrokerId ,
      RankId ,
      BrokerName ,
      RankName ,
      BrokerCode ,
      IntroducerCode ,
      CscName ,
      SIP ,
      Fresh ,
      FY ,
      SY ,
      TY ,
      Promotive ,
      Total ,
      NoOfPromotive ,
      PlanTypeName ,
      PlanYear ,
      CscId 

    )
    ( SELECT    BrokerId ,
                RankId ,
                BrokerName ,
                RankName ,
                BrokerCode ,
                IntroducerCode ,
                CscName ,
                SUM(SIP) AS 'SIP' ,
                SUM(Fresh) AS 'Fresh' ,
                SUM(FY) AS 'FY' ,
                SUM(SY) AS 'SY' ,
                SUM(TY) AS 'TY' ,
                Promotive ,
                Total = ISNULL(( SUM(SIP) ), 0) + ISNULL(( SUM(Fresh) ), 0)
                + ISNULL(( SUM(FY) ), 0) + ISNULL(( SUM(SY) ), 0)
                + ISNULL(( SUM(TY) ), 0) ,
                NoOfPromotive ,
                PlanTypeName ,
                PlanYear ,
                CscId
      FROM      CTE
      GROUP BY  BrokerId ,
                RankId ,
                BrokerName ,
                RankName ,
                BrokerCode ,
                IntroducerCode ,
                CscName ,
                Promotive ,
                NoOfPromotive ,
                PlanTypeName ,
                PlanYear ,
                CscId
    )

但是它给了我错误。如何在表中插入记录？谢谢。

Answer 1

试试这个 -

;WITH CTE AS 
(
    SELECT   ...
    FROM     @tmp
)
INSERT INTO dbo.tbl (....)
SELECT ..
FROM CTE
GROUP BY ...
ORDER BY ...