I am getting an error incorrect syntax near the keyword open. This is my first attempt at writing a loop in SQL 2005. I copied most of the code from another stored procedure on the database. What I am trying to do is to insert 3 values into a table called rptdata_monthly.dbo.rpt_dat_WMG_RevCtr.
The first variable is @rptpd the way the select is written I will only get one value from the select.
The second variable is @modalid I will get 57 differnt modalid's
The third variable is @modaldesc I will get 57 different modaldesc's
INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
VALUES(73,'WMG',@rptpd,3,'FARKAS, PAUL',@modalid,@modaldesc,31,0,0,2)
I am not sure what I am doing wrong. Any help is appreciated
Tom
The first variable is @rptpd the way the select is written I will only get one value from the select.
The second variable is @modalid I will get 57 differnt modalid's
The third variable is @modaldesc I will get 57 different modaldesc's
INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
VALUES(73,'WMG',@rptpd,3,'FARKAS, PAUL',@modalid,@modaldesc,31,0,0,2)
I am not sure what I am doing wrong. Any help is appreciated
Tom
Code:
set nocount on
DECLARE
@rptpd int
,@modalid int
,@modaldesc varchar(255)
declare bulk_insert cursor for
open bulk_insert
fetch next from bulk_insert into @modalid
while @@fetch_status = 0
begin
-- SET 3 variables @rptpd,modalid and modaldesc
SET @rptpd = (SELECT rptpd FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci='WMG' and rptpddiff =1)
SET @modalid = (SELECT modalid
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)
SET @modaldesc = (SELECT modaldesc
FROM rptdata_monthly.dbo.rpt_dic_Modal
WHERE clntid =73)
INSERT INTO rptdata_monthly.dbo.rpt_dat_WMG_RevCtr
VALUES(73,'WMG',@rptpd,3,'FARKAS, PAUL',@modalid,@modaldesc,31,0,0,2)
fetch next from bulk_insert into @modalid -- Next Record
end
close bulk_insert
deallocate bulk_insert