复制代码 代码如下:
.游标方式 1 declare @data nvarchar(max)
set @data=’1,tanw;2,keenboy’ –id,name
declare @dataitem nvarchar(100)
declare data_cursor cursor for (select * from split(@data,’;’))
open data_cursor
fetch next from data_cursor into @dataitem
while @@fetch_status=0
begin
declare @id int
declare @name nvarchar(50)
declare dataitem_cursor cursor for (select * from split(@dataitem,’,’))
open dataitem_cursor
fetch next from dataitem_cursor into @id
fetch next from dataitem_cursor into @name
close dataitem_cursor
deallocate dataitem_cursor
/*
在这里做逻辑处理，插入或更新操作 …
*/
end
close data_cursor
deallocate data_cursor
复制代码 代码如下:
.while方式
declare @data nvarchar(max)
set @data=’tanw,keenboy’ –id,name
declare @temp table
(
id int identity(1,1),
name nvarchar(50)
)
declare @id int
declare @name nvarchar(50)
declare @results nvarchar(max) set @results=”
insert into @temp select (select * from split(@data,’;’))
while exists(select * from @temp)
begin
select top 1 @id=id,@name=name from @temp
delete from @temp where [id] = @id
set @results=@results+@name+’,’
/*
在这里做逻辑处理，插入或更新操作 …
*/
end
select @results
如果是简单单表批量插入操作的，上面方法大可不必要