Iterate through records returned by a Query.

nitin singh

7y1.2k

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kalyan Kondapuram7y ago
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<=@Cnt
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 Kumar7y ago
Hi Nitin,

Could you please provide table structure so it will be easy yo understand and explain ?
Sreekanth Reddy7y ago
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 singh7y ago
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 Reddy7y ago
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.