Iterate through records returned by a Query.

Question

I have a query which returns few records. The Query result has a column 'ACCTID'. Now for each record i need to take the ACCTID and fetch record from other table. I tried it to do in C# but using loop, but it is taking lot of time. Pls suggest me how to do the same in SQL server side.

Overview of query is like below:

Main query:

Select NAME, CANCEL_DATE..., ACCTID from Table1;

What i need to do for ACCTID now is fetch data from two other tables for ACCTID fetched from above main query

:

select * from table2 where ACCTID=@ACCTID

select * from table3 where ACCTID=@ACCTID

In the end i need to return all the records fetched from above two queries.

Kalyan Kondapuram · Answer

Hi Nitin , As far as I undestood from your requirement , this should work for you : Declare @Cnt int ,@Rnk int ,@AcctId int if object_Id('tempdb..#ACCTID')is not null BEGIN Drop table #ACCTID end if object_Id('tempdb..#Final')is not null BEGIN Drop table #Final end Select Distinct ACCTID into #ACCTID from Table1; --- To fetch only ACCTID's from Main Query Select Row_Number()Over(Order by ACCTID)Rn ,ACCTID into #Final from #ACCTID Set @Rnk=1 select @Cnt=max(rn)from #Final While @Rnk BEGIN select @AcctId=AcctId from #Final where rn=@Rnk select * from table2 where ACCTID=@ACCTID ; select * from table3 where ACCTID=@ACCTID ; Set @Rnk=@Rnk+1 end #################################################### Mark as answer, If this helps you . Thank you.

Jignesh Kumar · Answer

Hi Nitin, Could you please provide table structure so it will be easy yo understand and explain ?

Sreekanth Reddy · Answer

step 1 : Firstly, bring all your table1 data to a temp table select col1,col2,ACCTID into #temp from table1 step 2 : Now loop the #temp by using while. Within the while loop I'll delete the iterated column from temp table. DECLARE @ACCTID varchar(max) = null; while exists (select * from #temp) begin select @ACCTID = (select top 1 ACCTID from #temp order by TableID asc) // Wite your logc... delete #temp where ACCTID = @ACCTID end step 3 : Drop thecreated temp table drop table #ControlTable Mark as answer, If it helps. Thank you.

nitin singh · Answer

Hi Sreekanth, Actually i dont need records after joining table 2 and table 3. ACCTID is not a direct column in Table 3. I need to take each record from the main query, and then for acctid of each record i need to fetch records from Table2 and table 3. I need a logic using which i can loop through each record of main query and using the ACCTID in that i shuould be able to fetch records from table 2 and table 3

Sreekanth Reddy · Answer

Hi Nitin, Do inner join between both tables : select * from table2 inner join table3 on table2.ACCTID = table3.ACCTID where table2.ACCTID=(select ACCTID from table1 where id=1) At the place of "id=1" take your column where condition which returns the exact one ACCTID. If it helps, Mark as answer. Thank you.