全部
基础类
应用实例
新技术前沿

求一存储过程：把一字符串以,分割开，然后一次性全部插入数据库表。急，在线等！

debug158 2008-02-28 01:24:15

s ='a,b,c,d,e,f'
QId = 1
我想把s插入表Hc_Research_Answer中，形成这样的数据：
id Hc_Answer_Content questiongId
1 a 1
2 b 1
3 c 1
4 d 1
5 e 1
6 f 1
请问该怎么实现？谢谢！

...全文

110 点赞收藏 15

写回复

15 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

Keambala 2008-02-28

Sorry , 应该是Create procedure , 我有修改过

回复赞

Keambala 2008-02-28

以前在网上看到有人用表函数来处理数组．希望可以帮助你：

alter function retrieveenum(@ss nvarchar(max))
returns @table Table(id int identity(1,1),number int)

as
begin
declare @s nvarchar(max)
declare @start int, @end int

select @s = @ss + ',', @start=1, @end = charindex(',' ,@s,@start)
while (@end> @start)
begin
insert into @table(number) values(substring(@s, @start,@end-@start))
select @start=@end+1, @end = charindex(',' ,@s,@start+1)
end
return
end

select * from retrieveenum('1,5,1,6,3,9,7,')

RESULT:

1 1
2 5
3 1
4 6
5 3
6 9
7 7

回复赞

dawugui 2008-02-28

都说完了.我接个分.

回复赞

青锋-SS 2008-02-28

难道我在4楼给的答案不可以?

回复赞

青锋-SS 2008-02-28

--如果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 行）

回复赞

QQQQAnnie 2008-02-28

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

回复赞

昵称被占用了 2008-02-28

偷懒出的错

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)

回复赞

wzy_love_sly 2008-02-28

我的不行吗

回复赞

debug158 2008-02-28

Haiwer 的报错：
-------------------------------------------------------------
服务器: 消息 170，级别 15，状态 1，过程 pr_insertData，行 10
第 10 行: 'rtrim' 附近有语法错误。

回复赞

wzy_love_sly 2008-02-28

写的乱，

回复赞

wzy_love_sly 2008-02-28



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

id Hc_Answer_Content Qid
1 a 1
2 b 1
3 c 1
4 d 1
5 e 1
6 f 1

回复赞

青锋-SS 2008-02-28

如果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 ''')
set @s='insert into tb(Hc_Answer_Content,questiongId) select '''+@s+''','+cast(@qid as varchar)
exec(@s)

回复赞

昵称被占用了 2008-02-28

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))