table1构成: id,mobile (id是自动编号)
现在通过存储过程来向表table1里插入记录:
存储过程里的输入参数:mobiles 是多个mobile的集合(之间用逗号分开,如: "5678909876,34567889,12345678")
现在是想把mobiles里的所有mobile(不在table1里的)插入到table1里.这里使用循环分割字符串了....偶都不会....谢谢了!
现在通过存储过程来向表table1里插入记录:
存储过程里的输入参数:mobiles 是多个mobile的集合(之间用逗号分开,如: "5678909876,34567889,12345678")
现在是想把mobiles里的所有mobile(不在table1里的)插入到table1里.这里使用循环分割字符串了....偶都不会....谢谢了!
set @str = '5678909876,34567889,12345678'...while charindex(',',@str)>0
begin
insert into mobiles select left(@str,charindex(',',@str)-1)
set @str = stuff(@str,1,charindex(',',@str),'')
endinsert into mobiles select @str...
declare @a varchar(20)
declare @i int
while(charindex(@str,',')<>0)
{
set @i= charindex(@str,',')
set @a=substring(@str,0,@i+1)
insert into..... --插入语句
set @str=right(@str,@i,len(@str)-@i)
}
搞混了 :(
libin_ftsafe(子陌红尘)的只能插入前了两个记录啊.还没有判断是否已经存在该记录了.
lsqkeke() 的也没考虑是否存在的问题啊,我还没试你的方法呢~~~~
set @str = '5678909876,34567889,12345678'while charindex(',',@str)>0
begin
if (select count(*) from table1 where name = left(@str,charindex(',',@str)-1))=0
insert into table1 select left(@str,charindex(',',@str)-1)
set @str = stuff(@str,1,charindex(',',@str),'')
endif (select count(*) from table1 where name = @str) =0
insert into table1 select @str这样测试OK了.
create function f_split(@str varchar(8000))
returns @tb table(num int)
as
begin
while charindex(',',@str)>0
begin
insert @tb
select left(@str,charindex(',',@str)-1) set @str=stuff(@str,1,charindex(',',@str),'')
end
insert @tb select @str
return
end
go--测试
declare @s varchar(100)
select @s='1,2,3,4,5,6,7,8'
select * from f_split(@s)--结果
/*
num
-----------
1
2
3
4
5
6
7
8(所影响的行数为 8 行)
*/
.
.
.
insert into mobiles select @str where not exists (select mobile from modiles where mobile =@str;lsqkeke() 的方法最后一个记录没插入;
----------------------------------------------------------------------------------
在循环体结束之后,执行了第三个记录的插入操作。
如果需要判断记录是否已经存在:declare @str varchar(8000)
set @str = '5678909876,34567889,12345678'...while charindex(',',@str)>0
begin
insert into mobiles select left(@str,charindex(',',@str)-1)
where not exists(select 1 from mobiles where 列名=left(@str,charindex(',',@str)-1))
set @str = stuff(@str,1,charindex(',',@str),'')
endinsert into mobiles select @str
where not exists(select 1 from mobiles where 列名=@str)...
强!谢谢各位了,结贴!.
set @str = '5678909876,34567889,12345678'create table tab(a varchar(20))declare @a varchar(20)
declare @i int
set @str=@str+','
while(charindex(',',@str)<>0)
begin
set @i= charindex(',',@str)
set @a=substring(@str,0,@i)insert into tab select @a
--select * from tab
set @str=right(@str,len(@str)-@i)
--select @str
endselect * from tab