Insert Into Temp Table from another Temp Table throws error

Question

I've seen a few of the questions and answers for this, but they all seem different than my problem. I am trying to insert into a temp table with a where clause from a real table to the id on a different temp table. Let me explain

Here is my first insert. It creates a temp table based on the parameters

Insert Into #programs (programs_id, state_program_ID, org_no, bldg_no)
    Select programs_ID, state_program_ID, org_no, bldg_no
    From programs as p
    Where p.org_no = @org_no
        And p.bldg_no = @bldg_no
        And p.school_yr = @school_year

This returns a table that has a flat list of programs. Programs are offered at the school level and are slightly modified from the related state_program.

Then I need a list of all students that have taken the program from the program_student table.

Insert Into #programStudent (programs_id , ss_id, status_id)
    Select ps.programs_id, ps.ss_id, ps.status_id
    From program_student as ps
    Where ps.programs_id = #programs.program_id 
        --'#programs.program_id' throws error

This would meet my need having all students that have taken any of the programs offered by the school at that school year.

The full error is

The multi-part identifier '#programs.program_id' could not be bound.

Answer 1

You are not addressing the #programs table in your second query. that last line will have to change to something like this:

WHERE EXISTS (SELECT TOP 1 1 FROM #Programs WHERE #Programs.programs_id = ps.program_id)

This is how you must address temp tables - they do not become variables in your current script - they are actual tables which get cleaned up after you disconnect. As such, they need to be introduced as tables - in a from clause, for each query that needs to reference them.

Answer 2

You had a typo when defining the #programs table - you called the column programs_id and not program_id. Just fix it, and you should be fine:

Insert Into #programs (program_id, state_program_ID, org_no, bldg_no)
-- "s" removed Here ---------^
    Select programs_ID, state_program_ID, org_no, bldg_no
    From programs as p
    Where p.org_no = @org_no
        And p.bldg_no = @bldg_no
        And p.school_yr = @school_year