sql server中批量插入与更新两种解决方案分享(存储过程)

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

/*
在这里做逻辑处理，插入或更新操作 ...
www.jb51.net
*/
end
close data_cursor
deallocate data_cursor

2.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+','
www.jb51.net
/*
在这里做逻辑处理，插入或更新操作 ...
*/
end
select @results

如果是简单单表批量插入操作的，上面方法大可不必要

作者 keenboy