How to create a temporary table or CTE with values from two sources

Need to build a temporary table or a CTE for reporting purposes. Users will be able to select a location and courses from drop-downs.

The tables involved are the Person table that holds all employees and a Common Table Expression that will have the courses selected from drop down.

I need to be able to create a temporary table or a CTE with employee id field from the person table and a course name field from Course CTE.

For example, if courses A, B, C are selected each employee will have three records, one for each Course selected. So employee 1 will have three records in this temporary table or CTE. I'm using SQL Server 2008.

1 answer

  • answered 2018-01-14 02:17 Jayasurya Satheesh

    You can use INNER JOIN or UNION queries inside CTE to get values from Multiple Tables. So If you want to get the EMployeeId and CourseName for Each employee, YOu can Join the tables inside the CTE like this

    ;WITH CTE
    AS
    (
        SELECT
            E.EmployeeId,
            C.CourseNm
            FROM dbo.Employee E
                INNER JOIN dbo.Course C
                    ON E.CourseId = C.CourseId
    )
    SELECT
        *
        FROM CTE
    

    if you want to use Temp tables, you can try this

    SELECT
        E.EmployeeId,
        C.CourseNm
        INTO #Temp
        FROM dbo.Employee E
            INNER JOIN dbo.Course C
                ON E.CourseId = C.CourseId
    
    SELECT * FROM #Temp