Glad you figured it out! 😀 Here's another way.

Even though you can insert data into a table using the output from the EXEC(@SQL), you might also just include the INSERT in your dynamic SQL string.

SELECT @TSQL = 'INSERT INTO SIS.dbo.PS_ACAD_PROG_Prosp_GradsTEST

SELECT * from OPENQUERY(SIS, ''SELECT *

from PS_ACAD_PROG APT

where ACAD_CAREER=''''GRAD'''' and EXP_GRAD_TERM='''''+@currSTRM+''''' and DEGR_CHKOUT_STAT=''''AP''''

and EFFDT=(select max(EFFDT) from PS_ACAD_PROG where EMPLID=APT.EMPLID and ACAD_CAREER=APT.ACAD_CAREER and STDNT_CAR_NBR=APT.STDNT_CAR_NBR)

and EFFSEQ=(select max(EFFSEQ) from PS_ACAD_PROG where EMPLID=APT.EMPLID and ACAD_CAREER=APT.ACAD_CAREER and STDNT_CAR_NBR=APT.STDNT_CAR_NBR and EFFDT=APT.EFFDT)'')'

EXEC(@TSQL)