Forum Discussion

MauryFrench's avatar
MauryFrench
Copper Contributor
Dec 06, 2018

INSERT fields after counting them in CTE

Good day everyone.

 

I have an SQL Server stored procedure that goes something like this. Ultimately it puts together multiple data items for a report.

 

cte_get_data1

cte_get_data2

cte_get_data3

 

SELECT *

 

FROM

cte_get_data1

inner join

cte_get_data2

inner join

cte_get_data3

 

Not quite that simple but you get the gist. Below, is one specific cte. I am still a newbie on SQL so don't laugh too hard.  As you can see, I am counting the number of students that fall within a certain range. My overall stored procedure works great; however I need to add code that will also insert each of the person_id values into a separate table in the database for use by another program, while keeping the count intact for processing with the entire stored procedure. Any ideas? Is this not going to be possible with my current programming style of running everything within a CTE then gluing the data items together?

 

I know I can do an insert using output from the cte, but I cannot figure out how to do the insert from this particular cte AND STILL collect the data from all the ctes combined.

 

Thank you in advance for your input.

 

cte_census_date_enrollment(censusenrcount,Course_id)--,reason)

       AS

       (

                    

              select CONVERT(VARCHAR(3),COUNT(person_id))

              ,course_sec

      

              from

              (     

                          

                     select

                           sac_person_id as person_id

                           ,SCS_COURSE_SECTION_ID as course_sec

                          

                     from

                     (

                            SELECT

                                          *     

                            FROM

                                   [COLLIVE].[dbo].[ENRL_ENROLLMENTS_Vw]

                           WHERE 

                                  ((SECTION_CENSUS_DATE >= @QTRBEGIN) AND (SECTION_CENSUS_DATE < @NEXTQTR))

             

                                  AND

                                  (SAC_CURRENT_STATUS IN ('R','A'))

                                  AND

                                  SECTION_ACAD_LEVEL = 'CE'

                                  and

                                  SAC_CRED_TYPE <> 'SE'

                                  and

                                  ISNULL(SAC_REASON,'99') <> '6' 

                     ) as courtesy_layer

      

              ) as studcount

       )

 

  • MauryFrench's avatar
    MauryFrench
    Copper Contributor

    Good morning, everyone. As a follow-up; I decided to work this from a completely different angle so this question becomes moot. Thank you for looking though. 

     

    Maury

Resources