Copy Code code as follows:
. cursor Mode 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
/*
Here to do the logical processing, insert or update operation ...
*/
End
Close Data_cursor
Deallocate data_cursor
Copy Code code as follows:
. While mode
declare @Data NVARCHAR (max)
set @Data = ' tanw,keenboy ' --id,name
;D eclare @Temp TABLE
(
Id INT IDENTITY (1,1),
name Nvarc HAR
)
declare @Id INT
declare @Name NVARCHAR ()
declare @Results nvarcha R (MAX) SET @Results = '
insert into @Temp Select (SELECT * from Split (@Data, '; '))
while EXISTS (SELECT * from @Temp)
begin
Select top 1 @Id =id, @Na Me=name from @Temp
DELETE from @Temp where [id] = @Id
SET @R esults= @Results + @Name + ', '
/*
& nbsp Here to do the logical processing, insert or update operation ...
*
end
select @Results
If it is a simple single table bulk insert operation, the above method is very unnecessary
