sql for loop to include returned values as part of a sql insert : i need to insert rows from another query into a single row that is then inserted into another table. it works if there is only one row returned; however, if the row count is greater than 1 it fails. i can't figure out the for loop - indicated with the --** section.


    declare @cn as int, @i as int
    set @cn = 569
    declare @dM table(dM varchar(max))
    declare @rowNum table (rowNum int)
    set @i = 0

    insert @rowNum
    exec ('select count(*) from table1 where c = ' + @cn)
    --select rowNum from @rowNum as NumberRows --return 2 rows
    
    if (select rowNum from @rowNum as NumberRows) > 1
    begin
    insert @dM
    exec ('select d.d + '' '' + o.o + '' '' + d.v as rtM from table where c = ' + @countNumber)
    --returns 2 rows as rtM so there will be two inserted rows
    --going now okay
    --going later okay

	--**
    --while (@i <= (select count(*) from @rowNum)) --didn't work
    --for each row returned in rtM in need to include as part of the overall insert
	insert into table2 (cn, rtM, idate)
	select
	@cn
	,'Message is: ' + (select dM from @dM) + ' - the message.'
	cz.idate + ' ' + qw.txt
	from table3 as cz
	inner join table4 as qw on cz.id = qw.id
	where cz.cn = @cn
	--set @i = @i + 1
	--**
	end
	else
	begin
	--there is only 1 row returned from rtM so there will be a single inserted row
	insert @dM
	exec ('select d.d + '' '' + o.o + '' '' + d.v as rtM from table where c = ' + @countNumber)
	insert into table2 (cn, rtM, idate)
	select
	@cn
	,'Message is: ' + (select dM from @dM) + ' - the message.'
	cz.idate + ' ' + qw.txt
	from table3 as cz
	inner join table4 as qw on cz.id = qw.id
	where cz.cn = @cn
	end
