Hi AllI have a query to insert records into a table based on a request but the query can only read one record at a time . How do i change the query such that it is able to read multiple records.In the below query i was able to input only 1 request which is 149906.Querydeclare @num_of_times intdeclare @Count INT DECLARE @newrequestid varchar(50)DECLARE @Frequency VARCHAR(25), @RequestId INT, @x INT, @Max INT, @RptDesc INTSET @RequestId = 149906SET @x = 1CREATE TABLE #RequestReports (xid int identity(1,1), RptDesc INT)INSERT INTO #RequestReports (RptDesc)SELECT RptDesc FROM selected_report where RequestId = @RequestIdSELECT @Frequency = defineschedule from report_request where requestid = @RequestIdIF (@Frequency = 'Quarterly')BEGIN select @num_of_times = ISNULL(datediff(month, receiveddate,expirationdate)/3, 0) from report_request where requestid = @RequestId IF @num_of_times >= 0 SET @num_of_times = @num_of_times + 1ENDELSE IF (@Frequency = 'Monthly')BEGIN select @num_of_times = ISNULL(datediff(month, receiveddate,expirationdate), 0) from report_request where requestid = @RequestId IF @num_of_times >= 0 SET @num_of_times = @num_of_times + 1ENDELSEBEGIN SET @num_of_times = 0ENDIF @num_of_times > 0 BEGINSELECT @Max = MAX(XID) FROM #RequestReportsWHILE (@x <= @Max)BEGINSELECT @RptDesc = RptDesc FROM #RequestReports WHERE XID = @xset @count = 1WHILE @count <= @num_of_timesBEGIN update table_keysset key_id = key_id + 1 where table_name = 'adhoc'Select @newrequestid = key_id from table_keys where table_name = 'adhoc' INSERT INTO [UnderWriting].[dbo].[renoffcyc] ([roc_id] ,[roc_ind] ,[fundtype] ,[prodmth] ,[opa_id] ,[hlthplnid] ,[targetdt] ,[actualdt] ,[freqcd] ) SELECT @newrequestid ,'O' ,RR.fundingtype ,case when @Frequency = 'Quarterly' Then convert(varchar(30),DATEADD (month , @count*3 ,RR.Receiveddate)) When @Frequency = 'Monthly' Then convert(varchar(30),DATEADD (month , @count+1 ,RR.Receiveddate)) End ,SR.Assignto ,RR.hlthplnid ,'' ,'' ,RR.defineschedul FROM [UnderWriting].[dbo].[REPORT_REQUEST] RR INNER JOIN SELECTED_REPORT SRON RR.requestid = SR.requestidwhere RR.requestid = @RequestId AND SR.RptDesc = @RptDescset @count = @count+1 ENDSET @x = @x + 1ENDENDDROP TABLE #RequestReports
Thanks in advance