DECLARE @IDS VARCHAR(100)SET @IDS = '100,200,30,8,3459,1'我要把这些ID循环插入到表Tables
表Tables中ID列插入后效果SELECT ID FROM TABLES-- ID-- 100
-- 200
-- 30
-- 8
-- 3459
-- 1小弟刚学 请大侠们帮下忙! 谢谢了!
表Tables中ID列插入后效果SELECT ID FROM TABLES-- ID-- 100
-- 200
-- 30
-- 8
-- 3459
-- 1小弟刚学 请大侠们帮下忙! 谢谢了!
功能:实现split功能的函数
*/create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as begin
declare @i intset @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
endif @inputstr <> '\'
insert @temp values(@inputstr)return
end
go--调用declare @s varchar(1000)set @s='1,2,3,4,5,6,7,8,55'select * from dbo.fn_split(@s,',')drop function dbo.fn_split /*
a
----------------------
1
2
3
4
5
6
7
8
55(所影响的行数为 9 行)
*/
create table tb(id varchar(10))
go
DECLARE @IDS VARCHAR(100)SET @IDS = '100,200,30,8,3459,1'while charindex(',',@ids) > 0
begin
insert into tb values(left(@ids,charindex(',',@ids)-1))
set @ids = substring(@ids,charindex(',',@ids) + 1 , len(@ids))
endinsert into tb values(@ids)select * from tbdrop table tb/*
id
----------
100
200
30
8
3459
1(所影响的行数为 6 行)
*/