Sql for 循环，用于将返回的值作为 sql 插入的一部分包含在内

Question

我需要将另一个查询中的行插入到单个行中，然后将其插入到另一个表中。

如果只返回一行，它就可以工作。但是，如果行计数大于 1，则会失败。

我无法弄清楚 for 循环 - 用 --** 部分指示。

declare @cn as int, @i as int
set @cn = 569
declare @dM table(dM varchar(max))
declare @rowNum table (rowNum int)
set @i = 1
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 table1 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

Answer 1

下面是动态 sql 没有意义的一个例子。

INSERT @dM
EXEC ('SELECT d.d + '' '' + o.o + '' '' + d.v AS rtM FROM table1 WHERE c = ' + @countNumber)

这可以在没有像这样的动态 sql 的情况下重写。

INSERT @dM
SELECT d.d + ' ' + o.o + ' ' + d.v as rtM FROM table1 WHERE c = @countNumber

这里更大的问题是您的查询根本无法在这里工作。动态 sql 与否，这都是无效的。您引用了名为 d 的对象或别名和名为 o 的对象或别名，但这两个对象或别名都不在查询中。