1. Cursor Mode
Copy Code code as follows:
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 ...
Www.jb51.net
*/
End
Close Data_cursor
Deallocate data_cursor
2.While Mode
Copy Code code as follows:
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 the =name from @Temp
DELETE from @Temp where [id] = @Id
SET @Results = @Results + @Name + ', '
Www.jb51.net
/*
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
Author Keenboy
