Insert Record from CTE to Table

Question

I am using SQL Server 2008. For getting some rows I am using a CTE in my stored procedure.

;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

It gives me correct data. Now I want to Insert that data into a table. I have tried like :

 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
    )

But It gives me error. How can I Insert record in table? Thanks.

"It gives me error" - what error? – Damien_The_Unbeliever Apr 29 '13 at 08:19 — Damien_The_Unbeliever, Apr 29 '13 at 08:19

Devart · Answer 1 · 2013-04-29T08:32:25.813

11

Try this one -

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

edited Apr 29 '13 at 08:32

answered Apr 29 '13 at 08:09

Devart

119,203
23
166
186

Doh. It's so obvious when you see it. – Swanny Jul 20 '15 at 04:15

score 3 · Answer 2 · answered Aug 26 '16 at 15:37

NOTE: This has been answered correctly for SQL-Server. But if you've stumbled upon this post, looking for the same answer but you're using some other DBMS (namely SYBASE, ORACLE, or maybe others), this won't work. You cannot use the INSERT statement immediately after the CTE. In these cases, try putting the insert statement first:

INSERT INTO someTable (Col1,Col2,Col3)
WITH CTE AS (
SELECT  someColA,
        someColB,
        someColC
FROM    anotherTable
)
SELECT  someColA,
        someColB,
        someColC
FROM    CTE

score 2 · Answer 3 · edited May 23 '17 at 12:09

2

You can insert directly from a cte into a table, here is one such example:

SO post

and another example:

CTE Insert

This may or may not help you with the SP, but you could always try a table valued function to return data:

Table valued function

edited May 23 '17 at 12:09

Community

1
1

answered Apr 29 '13 at 08:06

Ric

12,855
3
30
36

Mudassir Hasan · Answer 4 · 2013-04-29T08:17:16.020

CTE is destroyed as soon as it is used in a query . After the WITH CTE AS () query , you are doing a SELECT query which returns the data . But after that CTE is not availaible for INSERT query .

You need to do insert immediately after formulating CTE .

This is from MSDN

A common table expression (CTE) can be thought of as a temporary result set that is defined 
within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW 
statement. A CTE is similar to a derived table in that it is not stored as an object and lasts 
only for the duration of the query.

So this will work .

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
         )

Do INSERT immediately now

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
    )

Now you can select data from MyTable

Select * from MyTable

score 0 · Answer 5 · answered Oct 31 '13 at 03:42

0

You can not perform DML operation on CTE which contains aggregate function.

Check this post. http://adroitjam.com/cte-common-table-expression-insert-update-delete/

answered Oct 31 '13 at 03:42

nishantcomp2512

1
1

score 0 · Answer 6 · answered Jan 21 '14 at 09:55

0

Please remove braces for select query after insert query and execute complete code at once

It may work

answered Jan 21 '14 at 09:55

user3218628

1

score 0 · Answer 7 · answered Feb 28 '14 at 06:45

Declare a temporary table before your common expression table statement and before your select the data from common expression table put a insert statement before selecting the data into that temporary table you've created before.

DECLARE @MyTable TABLE
    ( BrokerId int,RankId int,BrokerName varchar,RankName varchar, BrokerCode varchar, IntroducerCode varchar, CscName varchar,SIP int,Fresh int 
     ,FY int,SY int,TY int,Promotive int,Total int,NoOfPromotive int,PlanTypeName int,PlanYear int,CscId int)

WITH [CTE] AS(
    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
)
INSERT  INTO @MyTable
SELECT  * FROM [CTE]
SELECT * FROM @MyTable

Insert Record from CTE to Table

7 Answers7