34,575
社区成员
发帖
与我相关
我的任务
分享
---------------------------------------------
--> Author : jinjazzli
--> Target : ---->1000
--> Date : 2009-12-11 17:07:02
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
create table #tb(code varchar(10),id varchar(10))
declare @str varchar(20),@s varchar(4000)
set @str='1@2@3@4'
set @s ='select ''@'','+ replace(@str,'@',' union all select ''@'',')
insert #tb exec(@s)
------------------------------------
select * from #tb
code id
---------- ----------
@ 1
@ 2
@ 3
@ 4
(4 行受影响)
drop table #tb
alter procedure [up_test]
( @code varchar(20),
@str varchar(1000)
)
AS
begin
select @str = 'insert into tb(code,a) select '''+@code+''', '''+
replace(@str,'@',''' union all select '''+@code+''', ''')+''''
exec(@str)
end
create table TB(col int)
declare @str varchar(100), @sql varchar(400)
set @str = '1@2@3@4@5'
select @sql=replace('@'+@str,'@',')'+char(10)+' insert into TB values(')
set @sql=right(@sql,len(@sql)-1)+')'
exec(@sql)
select * from TB
/*
col
-----------
1
2
3
4
5
(所影响的行数为 5 行)
*/
drop table TB
---------------------------------------------
--> Author : jinjazzli
--> Target : ---->1000
--> Date : 2009-12-11 17:07:02
--> Version: SQL Server 2005
---------------------------------------------
--> 测试数据: @tb
create table #tb(id varchar(10))
declare @str varchar(20),@s varchar(4000)
set @str='1@2@3@4'
set @s ='select '+ replace(@str,'@',' union all select ')
insert #tb exec(@s)
------------------------------------
select * from #tb
id
----------
1
2
3
4
(4 行受影响)
drop table #tb
create procedure [up_test]
(
@str varchar(1000) ---传入@str = '1@2@3@4@5'
)
AS
--------------我需要把@str以'@'分割,然后循环插入tb表里的a字段
begin
select @str = 'insert into tb(a) select '''+replace(@str,'@',''' union all select ''')+''
exec(@str)
end
create procedure [up_test]
(
@str varchar(1000) ---传入@str = '1@2@3@4@5'
)
AS
--------------我需要把@str以'@'分割,然后循环插入tb表里的a字段
begin
select @str = 'insert into tb(a) select '''+replace(@str,'@',''' union all select ''')+''
exec(@str)
end
declare @str varchar(8000)
set @str = '1@2@3@4@5'
set @str = 'select name='''+replace(@str,'@',''''+' union all select ''')+''''
exec(@str)
/*name
----
1
2
3
4
5
(5 行受影响)*/