September 9, 2016 at 8:42 am
I am encountering the following error:
An INSERT statement cannot contain a SELECT statement that assigns values to a variable.
Below is my sp. Any ideas?
declare @rows_deleted int
declare @rows_inserted int
declare @currSTRM varchar(4)
declare @TSQL varchar(8000)
set @currSTRM=(select OS_CURR_STRM from SIS.dbo.PS_OS_TERM_INFO_VW where ACAD_CAREER='GRAD')
print @currSTRM
--begin tran
-- Delete all rows from local table
--delete from SIS.dbo.PS_ACAD_PROG_Prosp_Grads
set @rows_deleted = @@rowcount --probably not necessary to know
-- Insert new rows from SIS table
insert into SIS.dbo.PS_ACAD_PROG_Prosp_GradsTEST
SELECT @TSQL = '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)
September 9, 2016 at 8:53 am
I found the solution.
I had to change to:
declare @rows_deleted int
declare @rows_inserted int
declare @currSTRM varchar(4)
declare @TSQL varchar(8000)
set @currSTRM=(select OS_CURR_STRM from SIS.dbo.PS_OS_TERM_INFO_VW where ACAD_CAREER='GRAD')
--begin tran
-- Delete all rows from local table
--delete from SIS.dbo.PS_ACAD_PROG_Prosp_Grads
set @rows_deleted = @@rowcount--probably not necessary to know
BEGIN
SET @TSQL = '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)'')'
END
-- Insert new rows from SIS table
insert into SIS.dbo.PS_ACAD_PROG_Prosp_GradsTEST
EXEC(@TSQL)
set @rows_inserted = @@rowcount
September 9, 2016 at 3:30 pm
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)
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply