34,838
社区成员




--如果id是自增列,可以这样
declare @s varchar(8000),@qid int
set @qid=1
set @s='a,b,c,d,e,f'
set @s=replace(@s,',',''','+cast(@qid as varchar)+' union all select ''')
--以下显示结果用,无实际意义
declare @sql varchar(8000)
set @sql='select '''+@s+''','+cast(@qid as varchar)
exec(@sql)
--以上显示结果用,无实际意义
set @s='insert into tb(Hc_Answer_Content,questiongId) select '''+@s+''','+cast(@qid as varchar)
exec(@s)
---- -----------
a 1
b 1
c 1
d 1
e 1
f 1
(所影响的行数为 6 行)
CREATE proc p1(@condition nvarchar(50))
AS
BEGIN
DECLARE @str nvarchar(500)
SET @Str=''
--concat string
SELECT @Str=@Str+','+e.[Name]
FROM table e
WHERE e.condition =@condition
select (STUFF(@Str,1,1,''))
END
create proc pr_insertData
@s varchar(30),
@QId int
as
declare @sql varchar(8000)
set @sql=replace(@s,',',''','+rtrim(@QId)+' union all select ''')+''','+rtrim(@QId)
exec( 'insert Hc_Research_Answer( Hc_Answer_Content,questiongId) select '''+@sql)
create table Hc_Research_Answer(id int identity(1,1),Hc_Answer_Content varchar(1),Qid int)
create proc proc_Hc_Research_Answer
@s varchar(100),
@QId int
as
begin
declare @sql varchar(8000)
select @sql=' select '''+replace(@s,',',''' as Hc_Answer_Content,'''+ltrim(@Qid)+''' as qid union all select ''')
set @sql='insert into Hc_Research_Answer(Hc_Answer_Content,Qid)'+ @sql+''','''+ltrim(@Qid)+''''
exec(@sql)
end
exec proc_Hc_Research_Answer 'a,b,c,d,e,f',1
select * from Hc_Research_Answer
create proc pr_insertData
@s varchar(30),
@QId int
as
declare @sql varchar(8000)
set @sql=replace(@s,',',''','+rtrim(@QId)+' union all select ''')
exec( 'insert Hc_Research_Answer( Hc_Answer_Content,questiongId) select '''+@sql+''','+rtrim(@QId))